combo box

[Harold Price]

I am trying to retrieve data from one of several tables
using the combobox. I am selecting the name of the table
from a prior selection and saving the table name in a
vacant cell. When I enter the cell address in the
listfillrange variable for the combobox it ether returns
the contents of the cell (table name), not the data in the
table or it will not accept the address at all. Is there a
way to use indirect addressing?

 

[Andy Brown]

You *can* use a dynamic named range as the data source for the combobox, but
it seems to need manually redefining listfillrange for this to kick in. So
you may have to use event code to force it to register.

Say your tables are in A:C, headed "Table1", Table2", "Table3". And that the
list lengths are variable, and that the cell containing the table name is
F1.

1. Use a dynamic named range eg: "Data",

=OFFSET(Sheet1!$A$1,1,MATCH(Sheet1!$F$1,Sheet1!$A$1:$C$1)-1,COUNTA(INDIRECT(
CHAR(MATCH(Sheet1!$F$1,Sheet1!$A$1:$C$1)+64)&":"&CHAR(MATCH(Sheet1!$F$1,Shee
t1!$A$1:$C$1)+64)))-1,1)

(via Insert -- Name -- Define ; and the CHAR portion only works up to 26
columns (A-Z)).

Then listfillrange = "Data" and you use this code in the worksheet module
(rightclick the sheet tab & View Code) ;

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$F$1" Then Exit Sub
ComboBox1.ListFillRange = "Data"
ComboBox1.ListIndex = -1
End Sub

HTH,
Andy