Named range - VBA - Excel 97

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

In VBA, given a particular cell, is it possible to determine the named range
to which the cell belongs?

TIA
Ben
 
Ben

See the message and the code below, from one of my posts from many 3 1/2
years ago.

HTH,
Bernie
MS Excel MVP
Start of quoted message
Click to expand...

Below are three macros and one function. The first macro will add a button
to the
right-click menu, the second will remove it, the third is called by the
added button, and
the function finds all named ranges that the activecell is part of. Give it
a try,
after copying it into your personal.xls.

HTH,
Bernie

Sub AddInRangeToCellDropDown()
With Application.CommandBars("Cell")
.Enabled = True
On Error Resume Next
.Controls("In Range?").Delete
With .Controls.Add(Type:=msoControlButton, Before:=1)
.Caption = "In Range?"
.Style = msoButtonIconAndCaption
.FaceId = 8
.OnAction = "ShowInNamedRanges"
End With
End With
End Sub

Sub RemoveNamedRangeFromCellDropDown()
With Application.CommandBars("Cell")
On Error Resume Next
.Controls("In Range?").Delete
End With
End Sub

Sub ShowInNamedRanges()
MsgBox InNamedRanges(ActiveCell)
End Sub

Function InNamedRanges(Optional inCell As Range) As String

Dim myName As Name
Dim myAddress As String
Dim myMessage As String
Dim myRange As Range
Dim inRange As Integer

If inCell Is Nothing Then Set inCell = Application.Caller
myMessage = "Cell " & inCell.Address(False, False) & " is not in a Range"
inRange = 0

On Error GoTo SkippedName
For Each myName In ActiveWorkbook.Names
myAddress = myName.RefersTo
Set B = Intersect(inCell, Range(myAddress))
If Not (B Is Nothing) Then
If inRange = 0 Then
inRange = 1
myMessage = "Cell " & B.Address(False, False) & " is in: " & myName.Name
Else: myMessage = myMessage & "; " & myName.Name
End If
End If
SkippedName:
Next myName
InNamedRanges = myMessage
End Function
 
Back
Top