List Box and Combo Box queries

[Eurekaonide]

Hi All

I wonder if you can advise on a query regarding list or combo boxes within a user form please?

1) I would like a list box or combo box to show all the options which are currently held within a certain column of my worksheet (This will be changing and adding as time goes on) and I would like this to be picked up automatically

2) I have 1a little combo boxe with the pre-determined items already added in but, I would like another combo box or list next to it to show a set given selection depending on which item is chosen in the first box.

can these be done?

 

[Andrew Poulsom]

You can create a dynamic named range and assign it to the ComboBox's RowSource property:

http://www.contextures.com/xlNames01.html#Dynamic

For your second question you can use the first ComboBox's Change event procedure to update the list in the second ComboBox. How you do that depends on how your data is laid out, but here is an example:

http://www.mrexcel.com/forum/showthread.php?t=33957

 

[Eurekaonide]

Thanks for this, I have the first one working.
2) however I am receiving an error message stating "Could Not Set the Row Source Property. Invalid Property Value"

Here is my code;

Private Sub ListBox2_Change()
    ListBox3.RowSource = "ProjectName" & ListBox2.ListIndex + 3
    ListBox3.ListIndex = -3
End Sub

I had used the +3 and -3 as the two cells are not adjacent to one another but 3 columns apart. Basically I suppose its a bit of a lookup as I want to select the entry now showing in the first ListBox2 and upon that selection I want the ListBox3 to show the data which is in Cell H of that same row? If you think you know of a better way of doing this would appreciate your thoughts.

 

[Andrew Poulsom]

Why do you need a ListBox for a single entry? Wouldn't a TextBox do the job? What is ProjectName?

 

[Eurekaonide]

Yes I have realised this and now changed it to a TextBox.

The "ProjectName" is the named range for the First lookup listBox2.

 

[Andrew Poulsom]

Try eg:

Private Sub ListBox2_Change()
    With ListBox2
        TextBox1.Text = Range(.RowSource).Cells(.ListIndex + 1, 4)
    End With
End Sub

 

[Eurekaonide]

This works fantastic - Thank you so very much