CommandCombo BOX want to populate all named ranges in it
 

Code Mention Below Populates all the sheetnames of the active workbook
in the combo box.... its not the combo box button its
COMMANDBARCOMBOBOX... now i want to a code snippet which should
populates all the named ranges in another COMMANDBARCOMBOBOX.. i am
newbie to vba.. so find this commandbar logic little complex.... hope
anyone can help me wit this.

Sub Auto_Open()

Dim cb As CommandBar
Dim ctrl As CommandBarControl
Dim ctrl1 As CommandBarControl
On Error Resume Next
Application.CommandBars("MyNavigator").Delete
On Error GoTo 0

Set cb = Application.CommandBars.Add(Name:="myNavigator",
temporary:=True)
With cb
.Visible = True
.RowIndex = msoBarRowLast
.Position = msoBarTop
Set ctrl = .Controls.Add(Type:=msoControlButton,
temporary:=True)
With ctrl
.Style = msoButtonCaption
.Caption = "Refresh Worksheet List"
.OnAction = "'" & ThisWorkbook.Name & "'!refreshthesheets"
End With
End Sub

Sub RefreshTheSheets()
Dim ctrl As CommandBarControl
Dim wks
Set ctrl = Application.CommandBars("myNavigator") _
.FindControl(Tag:="__wksnames__")
ctrl.Clear
For Each wks In ActiveWorkbook.Sheets
If wks.Visible = xlSheetVisible Then
ctrl.AddItem wks.Name
End If
Next wks
End Sub

CommandCombo BOX want to populate all named ranges in it
 

Try this code


Set BookNames = Application.Names
NamesCount = BookNames.Count

For Each Nm In BookNames
NmReferto = Nm.RefersTo
'remove equal sign
NmReferto = Replace(NmReferto, "=", "")
MsgBox ("Name : " & Nm.Name & " ; RefersTo : " & NmReferto)
'set to a variable
Set MyRange = Range(NmReferto)
Next Nm


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=167027

Microsoft Office Help