Indirect Function and Combobox (in worksheet)

[Bungraman]

Hi, I have done some searching and found one thread directly relating to my problem, but the solution in the thread lies in a broken link, so I apologize if I am doubling up here.

My problem is, I know the INDIRECT function will not work with a Combobox on a worksheet. So I wish to populate the combobox list referenced from a cell that contains the "named range" title inside. This cell will change the "named range" dependant on values of other cells.

For example (thinking outside the square):

The Combobox is positioned and referenced to "D6". The cell containing the "named range" title is "AC6". This cell (AC6) changes dynamically dependant on other cell values (through vlookup). How do I go about getting the Combobox to list the values from the named range shown in "AC6"? Also can this be done accross multiple rows, from row 6 to 25, as individual comboboxes on each row (ie D6, D7, D8 .... D25)?

Thanks

Bungra

 

[Bungraman]

Hi Glenn,

Not sure if I follow you .... what do you mean by a "breakpoint"?

Regards

 

[GlennUK]

With the code window open, click the mouse to the left of
ComboBox1.ListFillRange = ""

so that the line is highlighted brown with a brown dot to the left of the highlighted code. Also for testing with valid AC6 entries comment out the On Error Resume Next line of code, to see if that line of code works or errors out.

 

[Bungraman]

Hi Glenn,

I recreated the basic setup on a new sheet, and your code worked. But on the workbook that I need it to work it still comes up with a blank list in the combobox. I have other combobox's on the same sheet, but they are named something different to ComboBox1, and the named ranges reside on another sheet, should this affect the code? Logic tells me ... no.

Any clues?

Thanks

 

[GlennUK]

If the ranges are on another sheet you'd need:

    ComboBox1.ListFillRange = "Sheet2!" & Range("AC6").Value

changing the sheet name as required.

 

[Bungraman]

Thanks again Glenn,

Because each named range is quite long, there is about 15 named ranges that the list could be created from, these 15 named ranges come from 4 seperate sheets.
What would the code be to direct the "ListFillRange" to look in these four different sheets?

Regards

 

[GlennUK]

What happens if you really simplify it:

ComboBox1.ListFillRange = Range("AC6").Value

with unique names it should work.

 

[Bungraman]

Works a treat Glenn,

would you have a code to place what is selected in the combobox to populate the cell that it sits over.

i.e. if the combobox sits over D6, it is not visible until the user selects cell D6, then after the user makes their selection from the list, it populates that cell D6 with the selection.

Thanks

 

[GlennUK]

You can specify which cell updates from a combobox by setting the LinkedCell property. As for making it only visible on certain cell selections, do some experiments with the Worksheet_SelectionChange event.