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

try
Dim i as Integer
i= 5
with worksheets("Divisions")
set TeamPick.RowSource = range(.cells(4,i),.cells(11,i))
msgbox range(.cells(4,i),.cells(11,i)).address ' please delete
end with



Note this is untested.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Kieran,

Didn't work, but thanks anyway.
I'll play with it some more tomorrow.

John

"Kieran" wrote in message
...

try
Dim i as Integer
i= 5
with worksheets("Divisions")
set TeamPick.RowSource = range(.cells(4,i),.cells(11,i))
msgbox range(.cells(4,i),.cells(11,i)).address ' please delete
end with



Note this is untested.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

Dim i as Integer
i = 5
TeamPick.RowSource=Range("A4:A11").Offset(0,i-1).Address(External:=True)

from the immediate window:

i = 5
? Range("A4:A11").Offset(0,i-1).Address(External:=True)
[Book1]Sheet1!$E$4:$E$11

--
Regards,
Tom Ogilvy

"John Wilson" wrote in message
...
Kieran,

Didn't work, but thanks anyway.
I'll play with it some more tomorrow.

John

"Kieran" wrote in message
...

try
Dim i as Integer
i= 5
with worksheets("Divisions")
set TeamPick.RowSource = range(.cells(4,i),.cells(11,i))
msgbox range(.cells(4,i),.cells(11,i)).address ' please delete
end with



Note this is untested.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

Tom, et.al.,

My thanks to you and everyone else that responded.
I finally got a chance to try some of these suggestions out.

What I finally used was:

TeamPick.RowSource = _
Worksheets("Divisions"). _
Range("A19:A26").offset(0, DivPicked - 1). _
Address(External:=True)

John

"Tom Ogilvy" wrote in message
...
Dim i as Integer
i = 5
TeamPick.RowSource=Range("A4:A11").Offset(0,i-1).Address(External:=True)

from the immediate window:

i = 5
? Range("A4:A11").Offset(0,i-1).Address(External:=True)
[Book1]Sheet1!$E$4:$E$11

--
Regards,
Tom Ogilvy

"John Wilson" wrote in message
...
Kieran,

Didn't work, but thanks anyway.
I'll play with it some more tomorrow.

John

"Kieran" wrote in message
...

try
Dim i as Integer
i= 5
with worksheets("Divisions")
set TeamPick.RowSource = range(.cells(4,i),.cells(11,i))
msgbox range(.cells(4,i),.cells(11,i)).address ' please delete
end with



Note this is untested.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

The property row source accepts only strings not ranges, that´s the
reason why fails...

so, what you should use is simply:

TeamPick.RowSource = range(.cells(4,i),.cells(11,i)).address

Mika.

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

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

How about:

Dim i As Integer
Dim strCol As String
i = 5
strCol = Chr(i + 64)
TeamPick.RowSource = strCol & "4:" & strCol & "11"

--
HTH,
Dianne

In ,
John Wilson typed:
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

While it may not be an issue here, if

strCol = left(columns(i).Address(0,0), 2 + (i < 27))

it could handle an i value greater than 26.

--
Regards,
Tom Ogilvy

"Dianne" wrote in message
...
How about:

Dim i As Integer
Dim strCol As String
i = 5
strCol = Chr(i + 64)
TeamPick.RowSource = strCol & "4:" & strCol & "11"

--
HTH,
Dianne

In ,
John Wilson typed:
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

Nice one -- filed away.

--
Dianne

In ,
Tom Ogilvy typed:
While it may not be an issue here, if

strCol = left(columns(i).Address(0,0), 2 + (i < 27))

it could handle an i value greater than 26.


"Dianne" wrote in message
...
How about:

Dim i As Integer
Dim strCol As String
i = 5
strCol = Chr(i + 64)
TeamPick.RowSource = strCol & "4:" & strCol & "11"

--
HTH,
Dianne

In ,
John Wilson typed:
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