Column Number to letter in Row Source
set ListSource = Sheets("Divisions").Range(cells(FirstRow, Col),
cells(LastRow, Col))
Assumes the activesheet for the Cells objects. Either preface all with a
sheet reference or don't preface any and assume the activesheet.
Also, Rowsource is expecting a string, so you would need to do
TeamPick.RowSource = ListSource.Address
and probably better would be
TeamPick.RowSource = ListSource.Address(external:=True)
--
Regards,
Tom Ogilvy
"RADO" wrote in message
...
John,
you can set RowSource dynamically, not only in design mode
So, for the comboBox Initialize event (or activate or enter - you should
choose proper event to undate the combo box list - let me know if don't
know
how), use this code:
Dim FirstRow as integer
Dim LastRow as integer
Dim Col as integer
Dim ListSource as range
' point to the range you want, you can change that dynamically instead
of
fixed numbers
FirstRow = 4
LastRow=11
Col=5
set ListSource = Sheets("Divisions").Range(cells(FirstRow, Col),
cells(LastRow, Col))
TeamPick.RowSource = ListSource
It's untested, but should work
RADO
"John Wilson" wrote in message
...
Having a little trouble with this and can't seem to find it in Google.
The following works for ComboBox "TeamPick"
TeamPick.RowSource = "Divisions!B4:B11"
Problem is that I want to use an integer variable in place of
the column letter.
e.g.
Dim i as Integer
i = 5
TeamPick.RowSource should be range E4:E11
I know I could jury-rig this with a lookup table somewhere
but was hoping there was a simpler solution.
Thanks,
John
|