Named Range in combo Box

[monagan]

I have to combo Boxes in excel.

My named range, is the name that appears in Combo1

Combo2 needs to fillrange with named range.

Combo properties does not accept the named range..

Any advice?

 

[rdeleon]

You probably have to use VBA to capture the change event of combo1 like:

Private Sub ComboBox1_Change()
Dim i As Integer

Me.ComboBox2.Clear

For i = 1 To ActiveWorkbook.Worksheets(2).Range(Me.ComboBox1).Count
Me.ComboBox2.AddItem ActiveWorkbook.Worksheets(2).Range(Me.ComboBox1).Cells(i, 1)
Next i
End Sub

This assumes that your named ranges are on worksheet 2.

Hope it helps.

Rene'

 

[monagan]

Thanks Rene'

It did. Works great, I appreciate it

 

[01Patrik]

I believe you should concider achieving the same effect by using validation. Allow a list, "=namedRange", Instead of combo2, use the same thing, but write =indirect([cell-with-the-first-validation]).

Or in detail:



Presume the following data is entered in sheet2:

A B C
1 Fruit Apple Tomato
2 Salad Pear Lettuce
3 Orange
4
5

Define sheet2!A1:A2 as "firstchoice"
Define sheet2!B1:B3 as "Fruit"
Define sheet2!C1:C2 as "Salad"

Select a preferred cell (example sheet1!A1), choose 'validation' in the 'data' menu. In 'allow'-drop down menu, select 'list'. In the 'source' field, write "=firstchoice".

Select another preferred cell (example sheet1!A2), choose 'validation' in the 'data' menu. In 'allow'-drop down menu, select 'list'. In the 'source' field, write "=indirect(A1)" (in our example). There is of course no problems using vlookup and other functions in the 'source' field, as long as it refers to the same sheet and workbook (named ranges is a good way to get around that problem).


The reason I think you should use this is 1/ the features unde the other tabs in 'validation' - input message, error alert - and 2/ the security aspect not involving macros (making it easier for users with "high" security settings.





// Patrik