Populating combobox in custom toolbar
 

I have:

(1) a custom toolbar, represented by the object variable objJourneyBar
(2) added a combobox control (msoControlComboBox) represented by the
object variable objJourneyDepartCBox

I have a list of train stations on worksheet Sheet6, column A from row
1 to 3181. I have named that range rgUniqueStations.

I have tried a couple of methods to get the list of train stations
"into" the combobox on my toolbar, but no joy so far. Appreciate any
advice.

TIA,
Bill


--
Bill_excelforum
------------------------------------------------------------------------
Bill_excelforum's Profile: http://www.excelforum.com/member.php...o&userid=32280
View this thread: http://www.excelforum.com/showthread...hreadid=526912

Populating combobox in custom toolbar
 

Bill,
Does this work?...
Application.CommandBars(objJourneyBar).Controls(ob jJourneyDepartCBox).List = _
Worksheets("Sheet6").Range("rgUniqueStations").val ue
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Bill_excelforum"
wrote in message

I have:
(1) a custom toolbar, represented by the object variable objJourneyBar
(2) added a combobox control (msoControlComboBox) represented by the
object variable objJourneyDepartCBox

I have a list of train stations on worksheet Sheet6, column A from row
1 to 3181. I have named that range rgUniqueStations.

I have tried a couple of methods to get the list of train stations
"into" the combobox on my toolbar, but no joy so far. Appreciate any
advice.
TIA,
Bill

Populating combobox in custom toolbar
 

That doesn't seem to work....


--
Bill_excelforum
------------------------------------------------------------------------
Bill_excelforum's Profile: http://www.excelforum.com/member.php...o&userid=32280
View this thread: http://www.excelforum.com/showthread...hreadid=526912

Populating combobox in custom toolbar
 

Well so much for winging it.
It doesn't work for me either.
Here is some sample code that does work.
You should be able to modify and use it.
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

'---------------------
Sub MakeCustomToolbar()
Dim cmdBar As Office.CommandBar
Dim cmdComBox As Office.CommandBarControl
Dim rngList As Excel.Range
Dim lngN As Long

Set cmdBar = Application.CommandBars.Add("AnotherBeer", msoBarFloating, False)
Set cmdComBox = cmdBar.Controls.Add(msoControlComboBox)
Set rngList = Worksheets(1).Range("A1:A3181")

For lngN = 1 To 3181
cmdComBox.AddItem rngList(lngN).Value
Next
cmdBar.Visible = True

Set rngList = Nothing
Set cmdBar = Nothing
Set cmdComBox = Nothing
End Sub
'-----------

"Bill_excelforum"
<Bill_excelforum.25ev6n_1143601803.0705@excelfor um-nospam.com
wrote in message
That doesn't seem to work....
--
Bill_excelforum

Populating combobox in custom toolbar
 

Thanks, Jim. That works now.

As part of opening my workbook, I do a .CopyFromRecordset to a range
Then I name the range "rgUniqueStations". ( And I have Publi
rgUniqueStations As Range in the Declarations at the top of m
ThisWorkbook module.) My expectation was that I would use that range i
conjunction with .AddItem.

Jim, you declared another range:

Set rngList = Worksheets(1).Range("A1:A3181")

And used it in this code:

For lngN = 1 To 3181
cmdComBox.AddItem rngList(lngN).Value
Next

I tried substituting my range rgUniqueStations, but that didn't work a
all. My aim in doing that is to avoid hard coding the length of th
range (3181) because the number of stations in the list could chang
from time to time.

Anyway, thanks much for your help.
Bil

--
Bill_excelforu
-----------------------------------------------------------------------
Bill_excelforum's Profile: http://www.excelforum.com/member.php...fo&userid=3228
View this thread: http://www.excelforum.com/showthread.php?threadid=52691

Populating combobox in custom toolbar
 

Bill,

I am glad you got it working. However you raised a couple of questions/issues...
1. Module level variables in Class modules are private. So code in another
module won't know about it.
2. I would not use a Range variable with the same name as a Name object.
3. Even if you declare a public variable as a Range (in a standard module),
you must still Set the variable to a specific range before you can refer to it.
4 If you have a named range, that name will be available throughout the project.
So Worksheets(x).Range("rgUniqueStations") should work in all modules.
5. Or if you want to use that named range location on another sheet, you can...
Dim strRngName as String
strRngName = Worksheets(x).Range("rgUniqueStations").Address
Set rngList = Worksheets(y).Range("strRngName")
For lngN = 1 to rngList.Count
---
Jim Cone
San Francisco, USA


"Bill_excelforum"
wrote in message...
Thanks, Jim. That works now.

As part of opening my workbook, I do a .CopyFromRecordset to a range.
Then I name the range "rgUniqueStations". ( And I have Public
rgUniqueStations As Range in the Declarations at the top of my
ThisWorkbook module.) My expectation was that I would use that range in
conjunction with .AddItem.

Jim, you declared another range:

Set rngList = Worksheets(1).Range("A1:A3181")

And used it in this code:

For lngN = 1 To 3181
cmdComBox.AddItem rngList(lngN).Value
Next

I tried substituting my range rgUniqueStations, but that didn't work at
all. My aim in doing that is to avoid hard coding the length of the
range (3181) because the number of stations in the list could change
from time to time.

Anyway, thanks much for your help.
Bill


--
Bill_excelforum
------------------------------------------------------------------------
Bill_excelforum's Profile: http://www.excelforum.com/member.php...o&userid=32280
View this thread: http://www.excelforum.com/showthread...hreadid=526912

Populating combobox in custom toolbar
 

Follow up...

Set rngList = Worksheets(y).Range("strRngName")
should read
Set rngList = Worksheets(y).Range(strRngName)
(no quote marks)

Jim Cone



"Jim Cone"
wrote in message
Bill,
I am glad you got it working. However you raised a couple of questions/issues...
1. Module level variables in Class modules are private. So code in another
module won't know about it.
2. I would not use a Range variable with the same name as a Name object.
3. Even if you declare a public variable as a Range (in a standard module),
you must still Set the variable to a specific range before you can refer to it.
4 If you have a named range, that name will be available throughout the project.
So Worksheets(x).Range("rgUniqueStations") should work in all modules.
5. Or if you want to use that named range location on another sheet, you can...
Dim strRngName as String
strRngName = Worksheets(x).Range("rgUniqueStations").Address
Set rngList = Worksheets(y).Range("strRngName")
For lngN = 1 to rngList.Count
---
Jim Cone
San Francisco, USA