Microsoft KB Archive/134915

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

= 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 &quot;Activecell is in the range named &quot;&quot;&quot; & _ currentname.Name & &quot;&quot;&quot;&quot; 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.