Use Names of Named Ranges in ComboBox

[Eric Zest]

Hello, i have about 20 different named ranges in a worksheet. Each named
range highlights a different range of cells.

Is there a way to add the names of these named ranges to a combobox i have
in a userform? I'm trying to use the combox in the form to select just the
name of the named range, not the cells they will highlight. Hope i make
sense. Thanks in advance!

 

[Leith Ross]

Hello Eric,

Add this macro to the Declarations section of your UserForm. Change th
name of the worksheet from "Sheet2" to which sheet you want the name
ranges from and the name of the ComboBox from "ComboBox1" to what yo
are using.

========================================
Sub GetNamedRanges()

Dim NamedRng As Range

Set Wks = Worksheets("Sheet2")

For I = 1 To ThisWorkbook.Names.Count
X = ThisWorkbook.Names(I).RefersTo
On Error Resume Next
Set NamedRng = ThisWorkbook.Names(I).RefersToRange
If Err <> 0 Then
Err.Clear
Else
If Wks.Name = NamedRng.Parent.Name Then
ComboBox1.AddItem = NamedRng.Name.Name
End If
End If
Next I

End Sub
========================================

Sincerely,
Leith Ros

 

[Per Jessen]

Hi

You can only list all named ranges in the workbook you have to test if
the named range is in the desired sheet.

Change Sheet1 to the name of the desired sheet.

For Each n In ActiveWorkbook.Names
If n.RefersTo Like "*Sheet1*" Then
Me.ComboBox1.AddItem n.Name
End If
Next

Regards,
Per