XL: Macro to Determine Range Name of the Active Cell
  
PSS ID Number: Q134915
Article last modified on 08-21-1995
 
5.00 5.00c 7.00 | 5.00 5.00a
 
WINDOWS         | MACINTOSH
 

--------------------------------------------------------------------
The information in this article applies to:
 
 - Microsoft Excel for Windows, versions 5.0, 5.0c
 - Microsoft Excel for the Macintosh, versions 5.0, 5.0a
 - Microsoft Excel for Windows NT, version 5.0
 - Microsoft Excel for Windows 95, version 7.0
--------------------------------------------------------------------
 
SUMMARY
=======
 
This macro searches through all of the names in the workbook to find which
name or names the active cell is currently located in. For example, if the
current active cell is A5 on sheet1 and there is a defined name "Test" that
refers to "sheet1!A1:C5," the macro will display a message stating:
 
   The activecell is in the range named "Test"
 
MORE INFORMATION
================
 
Microsoft provides examples of Visual Basic procedures for illustration
only, without warranty either expressed or implied, including but not
limited to the implied warranties of merchantability and/or fitness for a
particular purpose. This Visual Basic procedure is provided 'as is' and
Microsoft does not guarantee that it can be used in all situations.
Microsoft does not support modifications of this procedure to suit customer
requirements for a particular purpose.
 
'This macro searches through all the defined names in a workbook to find
'the name(s) that the activecell falls within. For those names it finds
'it displays a message box with the range name in it
Sub Find_Name_ActiveCell_In()
 
    'Ignore any errors and may occur from some names referencing external
    'workbooks
    On Error Resume Next
    'Loop through all of the defined names in the workbook
    For Each currentname In Names
        'make sure the name is a valid cell reference
        Set rng = Range(currentname.Name)
        'check the error flag to make sure no error was generated by the
        'above line
        If Err = 0 Then
            'Test to see if the active cell is in the current named range
            If Not Intersect(ActiveCell, rng) Is Nothing Then
                'if it is then display the message box
                MsgBox "Activecell is in the range named """ & _
                    currentname.Name & """"
            End If
        Else
            'reset the error flag
            Err = 0
        End If
    Next
End Sub
 
KBCategory: kbprg kbcode
KBSubcategory:
 
Additional reference words: 7.00 5.00 find
=============================================================================
Copyright Microsoft Corporation 1995.
