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

 

[GlennUK]

Before anything else, what is the reason that you aren't using Data Validation and In-Cell drop-downs for this?

 

[Bungraman]

Hi Glenn,

Before anything else, what is the reason that you aren't using Data Validation and In-Cell drop-downs for this?

The reason for using a ComboBox was because a Data Validation drop-down list does not allow auto-fill/complete. One list that I have which is pertinent to this combobox has over 2000 entries to be listed so it would be good to type out a couple of letters to get to your selection.

Thanks
Bungra

 

[Bungraman]

... To clarify further, I would like the combobox to use the value in the cell, which happens to be a named range, to display the list. All attempts at this have ended with just the named range being displayed in the dropdown list (one value) not any of the values actually from the named range.

Should I be using the OFFSET or ADDRESS function anywhere to make this work as I have seen elsewhere on the web, I have absolutely no idea, because using them doesn't make sense.

Is it a simple case of coding to enable a reference from the cell to the combobox, and coding the combobox to read the value from the cell as a named range??? I am lost. Surely there is a easy solution.

Please there must be someone out there that can help.....

Regards

 

[GlennUK]

It'd be something like this, in the sheet code area ( right-click sheet tab, and choose View Code ):

Private Sub ComboBox1_GotFocus()

ComboBox1.ListFillRange = ""
On Error Resume Next
ComboBox1.ListFillRange = Range(Range("A2").Value).Address

End Sub

... changing A2 to be the cell that contains the name of the range.

 

[Bungraman]

Thanks Glenn,

I placed the code in the sheet concerned as said, and nothing happened, so I thought your "A2" should have been my "AC6" (named range) cell, and also nothing happened, should I be doing anything else? or missing something?

FYI, the combobox object is named ComboBox1, and number format of cell "AC6" is general.

Regards

Bungra

 

[Bungraman]

Bump

Putting this out for any other ideas....

 

[GlennUK]

What are the contents of AC6 exactly?

 

[Bungraman]

Hi Glenn, I thought you had abandoned the post.

The contents of AC6 is a general format cell that has the name of a "named range". This name varies depending on other formulas. The ComboBox is to recognise the contents of AC6 as a named range, and then list the contents of that named range in the combobox listing.

Does that make sense?

Regards

 

[GlennUK]

It sounds like the code should work ... did you try putting a breakpoint in to see if the code triggers?