Populate dropDown in a custom ribbon on clicking a button

johnjacobt

New Member
Joined
Nov 29, 2011
Messages
3
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 -

Code:
<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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

johnjacobt

New Member
Joined
Nov 29, 2011
Messages
3
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.
Code:
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

Code:
<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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,843
Members
414,342
Latest member
K Darrell Smith

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
Top