Microsoft KB Archive/811438

= Macro determines the range name of the active cell in Excel =

Article ID: 811438

Article Last Modified on 2/1/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 2001 for Mac
 * Microsoft Excel X for Mac

-



SUMMARY
This article provides a macro that searches through all the names in a Microsoft Excel 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 &quot;Test&quot; that refers to &quot;sheet1!A1:C5,&quot; the macro will display a message stating:

The activecell is in the range named &quot;Test&quot;



MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. 'This macro searches through all the defined names in a workbook to find 'the name(s) that the activecell appears in. For those names it finds 'it displays a message box with the range name in it ' Sub Find_Name_ActiveCell_In Dim oCurrentName as Name Dim rRng as Range ' 'Ignore any errors and may occur from some names referencing 'external workbooks '   On Error Resume Next ' 'Loop through all the defined names in the workbook '   For Each oCurrentName In Names ' 'make sure the name is a valid cell reference '       Set rRng = Range(oCurrentName.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, rRng) Is Nothing Then ' 'if it is then display the message box '               MsgBox &quot;Activecell is in the range named &quot; & _ oCurrentName.Name & &quot;&quot;&quot;&quot; End If       Else ' 'reset the error flag '           Err = 0 End If   Next End Sub

Additional query words: 2000 2001 2002 XP XLXP XLX XL2000 XL2002 XL XL2003 XL2007

Keywords: kbhowto KB811438

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.