CODEWITHSUNDEEP
excelcomboboxvba
David Ollodart
David Ollodart

Reputation: 23

Property "RowSource" Fails to Display on Combo Box

If I set the .RowSource property of a ListBox to a Named Range then in both the VBA display of the UserForm and the Excel display of the UserForm the row sources can be selected. However, with the ComboBox I only see this on the VBA display. If after setting the .RowSource property I try to .AddItem to the ComboBox object I get run time error 70, permission denied. 

Private Sub UserForm_Initialize()
    With pres_unit
        .AddItem "kPa"
        .AddItem "bar"
    End With
End Sub

How can I use a named range as a source for the drop down options of a ComboBox?

Excel screenshot of UserForm

VBA screenshot of UserForm

Upvotes: 2

Views: 1466

Answers (1)

Shai Rado
Shai Rado

Reputation: 33692

If you are using a NamedRange for a single Column, you can use the List property of the ComboBox:

Private Sub UserForm_Initialize()      

' create the Named Range "myNameRange" 
' you can manually (or with variables) modify the Range("B2:B10") in "Sheet1"
ThisWorkbook.Names.Add "myNameRange", Sheets("Sheet1").Range("B2:B10")

With pres_unit
    .List = Range("myNameRange").value
    'disallows user input, only values from list
    .Style = fmStyleDropDownList
End With

End Sub

Upvotes: 1

Related Questions