0

I have created a custom tab with a dropdown that has both items and buttons in it. I can get the onAction macro to run for a button, but cannot do the same for an item. Should this be possible? I have seen plenty of examples with onAction macros specified for items, but none seem to work. I also have an add-in witten in visual studio that has what appear to be items in a dropdown that call macros.

My code:

Private Sub Workbook_Activate()

' copied from here:
' https://stackoverflow.com/questions/8850836/how-to-add-a-custom-ribbon-tab-using-vba


Dim hFile As Long
Dim path As String, fileName As String, ribbonXML As String, user As String

hFile = FreeFile
user = Environ("Username")
path = "C:\Users\" & user & "\AppData\Local\Microsoft\Office\"
fileName = "Excel.officeUI"

ribbonXML = "<mso:customUI      xmlns:mso='http://schemas.microsoft.com/office/2009/07/customui'>" & vbNewLine
ribbonXML = ribbonXML + "<mso:ribbon><mso:qat/><mso:tabs><mso:tab id='x' label='Development' insertBeforeQ='mso:TabFormat'>" & vbNewLine 'insertAfterQ='x1:IDC_TEAM_TAB' id='mso_c1.1C4ECC7'
ribbonXML = ribbonXML + "<mso:group id='mso_c2.1C4ECD7' label='Group1' imageMso='Risks' autoScale='true'>" & vbNewLine
ribbonXML = ribbonXML + "<mso:dropDown id='dropDown' label='Test Menu:' onAction='test_macro'>" & vbNewLine
ribbonXML = ribbonXML + "   <mso:item id='item1' label='Item 1' onAction='test_macro'/>" & vbNewLine
ribbonXML = ribbonXML + "   <mso:item id='item2' label='Item 2'  onAction='test_macro'/>" & vbNewLine
ribbonXML = ribbonXML + "   <mso:item id='item3' label='Item 3'  onAction='test_macro'/>" & vbNewLine
ribbonXML = ribbonXML + "   <mso:button id='button' label='Button...' onAction='test_macro'/>" & vbNewLine
ribbonXML = ribbonXML + " </mso:dropDown>" & vbNewLine

ribbonXML = ribbonXML + "</mso:group>" & vbNewLine
ribbonXML = ribbonXML + "<mso:group id='mso_c3.1C56531' label='Group 2' imageMso='ListMacros' autoScale='true'/>" & vbNewLine
ribbonXML = ribbonXML + "</mso:tab></mso:tabs></mso:ribbon></mso:customUI>"

ribbonXML = Replace(ribbonXML, """", "")

Open path & fileName For Output Access Write As hFile
Print #hFile, ribbonXML
Close hFile

End Sub

Private Sub Workbook_Deactivate()

Dim hFile As Long
Dim path As String, fileName As String, ribbonXML As String, user As String

hFile = FreeFile
user = Environ("Username")
path = "C:\Users\" & user & "\AppData\Local\Microsoft\Office\"
fileName = "Excel.officeUI"

ribbonXML = "<mso:customUI           xmlns:mso=""http://schemas.microsoft.com/office/2009/07/customui"">" & _
"<mso:ribbon></mso:ribbon></mso:customUI>"

Open path & fileName For Output Access Write As hFile
Print #hFile, ribbonXML
Close hFile

End Sub

and:

Sub test_macro()
    Sheets("Sheet1").Select
    Cells(1, 1) = "test"
End Sub
Community
  • 1
  • 1
CHM
  • 1
  • 3

1 Answers1

0

There is an 'onaction' with the dropdown. You get an index for the item. In my example, you can chose between 3 languages in a dropdown in Excel UI ribbon. First item "English" is 0, second item "Français" is 1, and my third item "Nederlands" is 2. In blue what I adapt in xml: enter image description here

and in VBA, like for a button, I change my named constant value (or do whatever you want).

Sub DDonAction(control As IRibbonControl, id As String, index As Variant) Select Case control.id 'Case dropdown if multiple dropdowns Case "DDLanguage" Select Case index Case 0 'Action if English is selected ActiveWorkbook.Names("Language").RefersToR1C1 = "=""Eng""" Case 1 'Action if 'Français' is selected ActiveWorkbook.Names("Language").RefersToR1C1 = "=""Fr""" Case 2 'Action if Nederlands is selected ActiveWorkbook.Names("Language").RefersToR1C1 = "=""Nl""" End Select 'item End Select 'Dropdown End Sub

Kamolga
  • 21
  • 2