CODEWITHSUNDEEP
excelvbacombobox
PaulBarr
PaulBarr

Reputation: 949

Populating combobox from named ranges

I have created three dependent drop down lists using excel's validation formula. enter image description here The benefit of this is that it is easy to add more options and the dropdown list updates automatically. The structure for this is seen below (where each range is given the same name as the column header).

enter image description here

Is it possible to create the same effect using combo boxes. I can find examples of populating a combo box from hand but not automatically from named ranges

Upvotes: 2

Views: 17947

Answers (2)

Ramon Zwirs
Ramon Zwirs

Reputation: 11

i know this is an old post but i found this out by trial and error and wanted to share this with you guys.

when you create a namedrange and want to use that info in your combobox then i have a simple solution here.

for example namedrange is called in your case "Headers" then do the following

Me.combobox1.RowSource = "Headers"

I don't know why but its adding the info from the named range.

Upvotes: 1

Davesexcel
Davesexcel

Reputation: 6982

Here is something you can practice with.

Create Combobox1

Populate with a worksheet_selection Change event, the headers range is named "Headers"

enter image description here

The range below the headers are named according to the header names.

Populate combobox2

enter image description here

Change combobox1 to populate combobox2

The Code

Goes into the worksheet module.

Private Sub ComboBox1_Change()
    Dim s As String
    s = ComboBox1
    Me.ComboBox2.List = Range(s).Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ComboBox1.List = Application.WorksheetFunction.Transpose(Range("Headers"))
End Sub

Upvotes: 1

Related Questions