Dynamic CustomUI Combobox

Jeffrey Smith

Jeffrey Smith

Well-known Member
Joined
Feb 11, 2005
Messages
795
After several days of searching this site and many others, I Think I'm on my to understanding the basics of creating my own custom ribbon menu. I found the "CustomUIEditor" and Andy Pope's RibbonX Visual Designer. Both great tools.

I've created my menu. It has a new tab, two group, and some controls. My task is to load strings into one of the comboboxes from a list on a sheet. The id of the combobox is cmbo_ReachesCombo. I grabbed some code from Andy Pope's site that changes the text for a editbox. I tried to adapt it to my combobox, but it didn't work. Can anyone help with specific code for loading a combobox on the ribbon?

Jeff


This is the xml
Rich (BB code): 
<CUSTOMUI target="_blank" customui? 01 2006 office schemas.microsoft.com http:  xmlns="<a href="><HTTP: 01 2006 office schemas.microsoft.com A customui<><FONT color=#0000ff size=2><FONT color=#0000ff size=2><P><!--</FONT></FONT><FONT color=#008000 size=2><FONT color=#008000 size=2>RibbonX Visual Designer 1.7 for Microsoft Excel. XML Code produced on 2010/01/04</FONT></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>--></P><P><</FONT></FONT><FONT color=#800000 size=2><FONT color=#800000 size=2>customUI </FONT></FONT><FONT color=#ff0000 size=2><FONT color=#ff0000 size=2>xmlns</FONT></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>=</FONT></FONT><FONT size=2>"http://schemas.microsoft.com/office/2006/01/customui" ></P><ribbon >
<tabs >
<tab 
id="TSTab"
label="Time Series"
tag="TS">
<group 
id="grpNavigate"
imageMso="UpgradeWorkbook"
label="Navigate"
tag="TS">
<comboBox 
id="cmbo_ReachesCombo"
imageMso="DiagramReverseClassic"
label="Goto Reaches"
screentip="Select a Reach Page to Show"
tag="TS"
getText="cmbo_ReachesCombo_getText"
onChange="cmbo_ReachesCombo_onChange"/>
<button 
id="btn_FlowSum"
imageMso="ReturnToTaskList"
label="Flow Summary"
screentip="Show the Flow Summary Page"
tag="TS"
onAction="btn_FlowSum_onAction"/>
<button 
id="btn_SpeciesList"
imageMso="DatasheetNewField"
label="Species List"
screentip="Show the Species List Page"
tag="TS"
onAction="btn_SpeciesList_onAction"/>
</group >
<group 
id="grpOptions"
imageMso="PageBreakInsertOrRemove"
label="Split Screen Options"
tag="TS">
<comboBox 
id="cmbo_OptionList"
imageMso="DatasheetView"
label="Option List"
screentip="Choose an Option to Show"/>
<button 
id="btn_OptionsOff"
imageMso="ErrorChecking"
label="Turn off Options"
screentip="Turn off"
onAction="btn_OptionsOff_onAction"/>
</group >
</tab >
</tabs >
</ribbon >
</customUI >


Rich (BB code):
Rich (BB code):
Rich (BB code):
Rich (BB code):
Rich (BB code): 
Public Sub cmbo_ReachesCombo_getText(control As IRibbonControl, ByRef returnedVal)
'
' Code for getText callback. Ribbon control Reaches Combobox
'
  Dim R As Range
  Set R = Sheets("Options").Range(Range("ReachPagesHeader").Offset(1, 0), Range("ReachPagesHeader").Offset(20, 0).End(xlUp))
    
    returnedVal = R.text
    
End Sub


 
Last edited:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
Ok, So there's not a lot of people that have worked with the ribbon. I've been trying to find the answer elsewhere. All of the sites seem to give the same answer. I've revised my code.

All I'm trying to do is fill a combobox on a custom ribbon from a list on a sheet. I would appreciate any help, even if it's just example code from somebody that is also trying to create a combobox on the ribbon.

Here is the xml

Rich (BB code): 
<!--RibbonX Visual Designer 1.7 for Microsoft Excel. XML Code produced on 2010/01/04-->
<customUI  xmlns="http://schemas.microsoft.com/office/2006/01/customui" ******="myRibbon_******">
 <ribbon >
  <tabs >
   <tab 
    id="TSTab"
    label="Time Series"
    tag="TS">
    <group 
     id="grpNavigate"
     imageMso="UpgradeWorkbook"
     label="Navigate"
     tag="TS">
     <comboBox 
      id="cmbo_ReachesCombo"
      imageMso="DiagramReverseClassic"
      label="Goto Reaches"
      screentip="Select a Reach Page to Show"
      tag="TS"
      sizeString="WWWWWWWWWWWWWWW"
      getItemID="cmbo_ReachesCombo_getID"
      getItemCount="cmbo_ReachesCombo_getCount"
      getItemLabel="cmbo_ReachesCombo_getLabel"
      onChange="cmbo_ReachesCombo_onChange"/>
     <button 
      id="btn_FlowSum"
      imageMso="ReturnToTaskList"
      label="Flow Summary"
      screentip="Show the Flow Summary Page"
      tag="TS"
      onAction="btn_FlowSum_onAction"/>
     <button 
      id="btn_SpeciesList"
      imageMso="DatasheetNewField"
      label="Species List"
      screentip="Show the Species List Page"
      tag="TS"
      onAction="btn_SpeciesList_onAction"/>
    </group >
    <group 
     id="grpOptions"
     imageMso="PageBreakInsertOrRemove"
     label="Split Screen Options"
     tag="TS">
     <comboBox 
      id="cmbo_OptionList"
      imageMso="DatasheetView"
      label="Option List"
      screentip="Choose an Option to Show"/>
     <button 
      id="btn_OptionsOff"
      imageMso="ErrorChecking"
      label="Turn off Options"
      screentip="Turn off"
      onAction="btn_OptionsOff_onAction"/>
    </group >
   </tab >
  </tabs >
 </ribbon >
</customUI >
 
Last edited:
Upvote 0
This dang site is screwing up the posts with code.





Rich (BB code): 
<!--RibbonX Visual Designer 1.7 for Microsoft Excel. XML Code produced on 2010/01/04-->
<customUI  xmlns="http://schemas.microsoft.com/office/2006/01/customui" ******="myRibbon_******">
 <ribbon >
  <tabs >
   <tab 
    id="TSTab"
    label="Time Series"
    tag="TS">
    <group 
     id="grpNavigate"
     imageMso="UpgradeWorkbook"
     label="Navigate"
     tag="TS">
     <comboBox 
      id="cmbo_ReachesCombo"
      imageMso="DiagramReverseClassic"
      label="Goto Reaches"
      screentip="Select a Reach Page to Show"
      tag="TS"
      sizeString="WWWWWWWWWWWWWWW"
      getItemID="cmbo_ReachesCombo_getID"
      getItemCount="cmbo_ReachesCombo_getCount"
      getItemLabel="cmbo_ReachesCombo_getLabel"
      onChange="cmbo_ReachesCombo_onChange"/>
     <button 
      id="btn_FlowSum"
      imageMso="ReturnToTaskList"
      label="Flow Summary"
      screentip="Show the Flow Summary Page"
      tag="TS"
      onAction="btn_FlowSum_onAction"/>
     <button 
      id="btn_SpeciesList"
      imageMso="DatasheetNewField"
      label="Species List"
      screentip="Show the Species List Page"
      tag="TS"
      onAction="btn_SpeciesList_onAction"/>
    </group >
    <group 
     id="grpOptions"
     imageMso="PageBreakInsertOrRemove"
     label="Split Screen Options"
     tag="TS">
     <comboBox 
      id="cmbo_OptionList"
      imageMso="DatasheetView"
      label="Option List"
      screentip="Choose an Option to Show"/>
     <button 
      id="btn_OptionsOff"
      imageMso="ErrorChecking"
      label="Turn off Options"
      screentip="Turn off"
      onAction="btn_OptionsOff_onAction"/>
    </group >
   </tab >
  </tabs >
 </ribbon >
</customUI >
 
Upvote 0
I can't post xml code

I'll post it outside the code structure

<!--RibbonX Visual Designer 1.7 for Microsoft Excel. XML Code produced on 2010/01/04-->
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" ******="myRibbon_******">
<ribbon >
<tabs >
<tab
id="TSTab"
label="Time Series"
tag="TS">
<group
id="grpNavigate"
imageMso="UpgradeWorkbook"
label="Navigate"
tag="TS">
<comboBox
id="cmbo_ReachesCombo"
imageMso="DiagramReverseClassic"
label="Goto Reaches"
screentip="Select a Reach Page to Show"
tag="TS"
sizeString="WWWWWWWWWWWWWWW"
getItemID="cmbo_ReachesCombo_getID"
getItemCount="cmbo_ReachesCombo_getCount"
getItemLabel="cmbo_ReachesCombo_getLabel"
onChange="cmbo_ReachesCombo_onChange"/>
<button
id="btn_FlowSum"
imageMso="ReturnToTaskList"
label="Flow Summary"
screentip="Show the Flow Summary Page"
tag="TS"
onAction="btn_FlowSum_onAction"/>
<button
id="btn_SpeciesList"
imageMso="DatasheetNewField"
label="Species List"
screentip="Show the Species List Page"
tag="TS"
onAction="btn_SpeciesList_onAction"/>
</group >
<group
id="grpOptions"
imageMso="PageBreakInsertOrRemove"
label="Split Screen Options"
tag="TS">
<comboBox
id="cmbo_OptionList"
imageMso="DatasheetView"
label="Option List"
screentip="Choose an Option to Show"/>
<button
id="btn_OptionsOff"
imageMso="ErrorChecking"
label="Turn off Options"
screentip="Turn off"
onAction="btn_OptionsOff_onAction"/>
</group >
</tab >
</tabs >
</ribbon >
</customUI >
 
Upvote 0
Allright, so I'll just post the code from the xml that is important

<comboBox
id="cmbo_ReachesCombo"
imageMso="DiagramReverseClassic"
label="Goto Reaches"
screentip="Select a Reach Page to Show"
tag="TS"
sizeString="WWWWWWWWWWWWWWW"
getItemID="cmbo_ReachesCombo_getID"
getItemCount="cmbo_ReachesCombo_getCount"
getItemLabel="cmbo_ReachesCombo_getLabel"
onChange="cmbo_ReachesCombo_onChange"/>
 
Upvote 0
Good now I can post the VBA code


ThisWorkbook module

Code: 
Private Sub Workbook_Open()
  
  Dim R As Range, Cell As Range, X As Integer
  Set R = Sheets("Options").Range(Range("ReachPagesHeader").Offset(1, 0), Range("ReachPagesHeader").Offset(20, 0).End(xlUp))
    
  ReachCmboItems = R.Count - 1
  ReDim ReachCmboArray(ReachCmboItems)
  X = -1
  For Each Cell In R
    X = X + 1
    ReachCmboArray(X) = Cell.text
  Next Cell
    
End Sub


A normal module
Code: 
Dim ReachCmboItems As Integer, ReachCmboArray() As String, ReachCmboTxt As String
Public TSRibbon As IRibbonUI
Private Sub myRibbon_******(ribbon As IRibbonUI)
   Set TSRibbon = ribbon
End Sub
 
'Callback for Combobox1 getItemID (NOT GIVEN BY CALLBACK GENERATOR)
Sub cmbo_ReachesCombo_getID(control As IRibbonControl, index As Integer, ByRef ID)
  
  ID = "Reach" & index
End Sub
 
'Callback for cmbo_ReachesCombo getItemCount
Private Sub cmbo_ReachesCombo_getCount(control As IRibbonControl, ByRef returnedVal)
  
  returnedVal = ReachCmboItems
  
End Sub
'Callback for cmbo_ReachesCombo getItemLabel
Private Sub cmbo_ReachesCombo_getLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
  
  returnedVal = ReachCmboArray(index)
  
End Sub
'Callback for cmbo_ReachesCombo onChange
Private Sub cmbo_ReachesCombo_onChange(control As IRibbonControl, text As String)
  ReachCmboTxt = text
  MsgBox ReachCmboTxt
End Sub
 
Upvote 0
Have you tried example 2 under section 3 on this page? Seems to be what you want.
 
Upvote 0
Yeah Rory, I used some code in those examples. I think I'm going to have to load my custom ribbon in a new blank workbook and see if I have the same problem. Maybe I have some interfering code. Unless you have any other suggestions.

Jeff
 
Upvote 0
Ok, this problem solved. I used Dim in the standard module instead of PUBLIC. "ThisWorkbook" section didn't complain when trying to use the variables not declared public. I created a new blank workbook and tried it there. It finally gave the error and I worked it out. Geez, that only took a day and a half to figure out!!!

Code: 
PUBLIC ReachCmboItems As Integer, ReachCmboArray() As String, ReachCmboTxt As String
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
Adding icons to ribbon
Replies
4
Views
149
tiredofit
T
M
  • Solved
Custom Ribbon Button Size in RibbonX
Replies
2
Views
736
mallkins
M
A
  • Question
Custom Ribbon UI <ribbon startFromScratch="true"> not working on MacOS
Replies
0
Views
727
andrevni22
A
U
  • Question
Excel Ribbon button icon change effect
Replies
3
Views
527
jkpieterse
jkpieterse
B
  • Question
ToggleButton on Ribbon - change ribbon icon
Replies
1
Views
319
BenW71
B

Forum statistics

Threads
1,214,559
Messages
6,120,208
Members
448,951
Latest member
jennlynn

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top