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!

Hello Eric,

Add this macro to the Declarations section of your UserForm. Change the
name of the worksheet from "Sheet2" to which sheet you want the named
ranges from and the name of the ComboBox from "ComboBox1" to what you
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 Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=51219

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

On 16 Jan., 18:24, Eric Zest <Eric
wrote:
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!