 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / March 2006Populating 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  SignatureBill_excelforum Bill, Does this work?... Application.CommandBars(objJourneyBar).Controls(objJourneyDepartCBox).List = _ Worksheets("Sheet6").Range("rgUniqueStations").value 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 That doesn't seem to work.... SignatureBill_excelforum 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@excelforum-nospam.com> wrote in message That doesn't seem to work.... SignatureBill_excelforum 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, 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 SignatureBill_excelforum Follow up... Set rngList = Worksheets(y).Range("strRngName") should read Set rngList = Worksheets(y).Range(strRngName) (no quote marks) Jim Cone "Jim Cone" <jim.coneXXX@rcn.comXXX> 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.