Populate dropDown in a custom ribbon on clicking a button

[johnjacobt]

Hi All,

I'm relatively new to the CustomUI stuff. Today I created a custom Tab in Ribbon with a button and a dropDown (in Excel 2010). The XML looks like below -

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <tabs>
      <tab id="MyTab" label="MyTab" insertBeforeMso="TabHome">
        <group id="Group1" label="My Group">
          <button id="Button1" label="Click Me" size="large" onAction="Macro1" />
          <dropDown id="dropDown1" label="Drop Down" />
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

What I want to achieve is to write some code in Macro1 that will populate values One, Two, Three in the dropDown when Button1 is clicked. That means when the xlsm file is opened dropDown would be empty. It will be populated with values only when button is clicked.

Can any of you write a code snippet to get this done?
If any changes should be made to the XML (like adding callback actions) I have posted above please do so as well.

Thanks in advance. Eagerly waiting for the answer.

- johnjacobt

 

[johnjacobt]

I've got no patience

 

[johnjacobt]

Hi All,

Here is the solution to the above stated situation. Courtesy: Andy Pope
Reference: MS Technet Forum

Use this code in a standard code module of the spreadsheet.

Private m_rbxUI As IRibbonUI
Private m_lngNItems As Long
Public Sub dropDown1_getItemCount(control As IRibbonControl, ByRef returnedVal)
'
' Code for getItemCount callback. Ribbon control dropDown
'
    returnedVal = m_lngNItems
End Sub

Public Sub rbx_******(ribbon As IRibbonUI)
'
' Code for ****** callback. Ribbon control customUI
'
    Set m_rbxUI = ribbon
    m_lngNItems = 0
    
End Sub
Public Sub dropDown1_getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
'
' Code for getItemLabel callback. Ribbon control dropDown
'
    returnedVal = "Item " & index
    
End Sub
Sub Macro1(control As IRibbonControl)
    
    m_lngNItems = 3
    m_rbxUI.Invalidate
    
End Sub

And this xml

<customUI 
	xmlns="http://schemas.microsoft.com/office/2006/01/customui"
	******="rbx_******">
	<ribbon >
		<tabs >
			<tab 
				id="MyTab"
				insertBeforeMso="TabHome"
				label="MyTab">
				<group 
					id="Group1"
					label="My Group">
					<button 
						id="Button1"
						label="Click Me"
						size="large"
						onAction="Macro1"/>
					<dropDown 
						id="dropDown1"
						label="Drop Down"
						getItemCount="dropDown1_getItemCount"
						getItemLabel="dropDown1_getItemLabel"/>
				</group >
			</tab >
		</tabs >
	</ribbon >
</customUI >

You need to make use of the two getItem callbacks to set number of elements and the text used.