Hi there, I have the following macro below which I came across in a forum and creates a dropdown list on the ribbon. It does exactly what I want, except that I want to insert a defined named range into the array in line 5 of the VBA so that the dropdown list is not hardcoded within the macro. I would like to replace "1", "2", "3", "4" with a defined name range that updates as you amend the name range.

Thanks in advance for your help.

Ben


Sub MakeTerriList()
Dim TBar As CommandBar
Dim NewDD As CommandBarControl
Dim TerriArray() As String
Dim AllTerri As Variant
AllTerri = Array("1", "2", "3", "4")

' Delete existing toolbar if it exists
On Error Resume Next
CommandBars("TerriList").Delete
On Error GoTo 0

' Create a new toolbar
Set TBar = CommandBars.Add
With TBar
.Name = "TerriList"
.Visible = True
End With

' Add a DropDown control
Set NewDD = CommandBars("TerriList").Controls.Add _
(Type:=msoControlDropdown)
With NewDD
.Caption = "Terri11"
.OnAction = "PasteTerri"
.Style = msoButtonAutomatic

' Fill it with Territory name
For i = 0 To UBound(AllTerri, 1)
.AddItem AllTerri(i)
Next i
.ListIndex = 1
End With
End Sub