Populate Combo box from autofilter list 1

[Jod1959a]

Excel 2007
VBA question

I have written a sub to fill a user-form combo box based on the content of a Excels tables header auto-filter list. When I hard code the column header name it works fine. The problem I'm having is trying to make it dynamic.

The line of code I need to be able to dynamicly change is this.

Set AllCells = Range("Table1[[#ALL],[Invoiced_By]]")

I would like to use a variable to replace the Table header name ([Invoiced_By]).

Dim Hvar as String

Hvar = Toolbar.cboList.value

Set AllCells = Range("Table1[[#ALL],Hvar]")


When I try I keep getting an error "Method Range of Object Global Failed" . I think my syntax is wrong.

Any help would be greatly appreciated

 

[SkipVought]

hi,

Table refernces cannot be used with variables.

You could try something like this IF evey cell in your table has a value and assuming that your headin ROW is 1...

set AllCells = Rows(1).Find(Hvar).offset(1)
Set AllCells = Range(AllCells, AllCells.end)xldown))

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Jod1959a]

Thanks Skip that worked like a dream. I wonder why you can't use a variable for a table header?

 

[Jod1959a]

SOLVED

 

[SkipVought]

give this a try

Set AllCells = Range("Table1[" & Hvar & "]")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}