CODEWITHSUNDEEP
vbapowerpointribbonribbon-controlribbonx
AndyK
AndyK

Reputation: 21

Get value from Ribbon Control (Drop Down or Combo Box etc.)

Is it possible to design a ribbon addin with either drop down list or combo box and then use the values to run a subroutine?

I created a PowerPoint Ribbon Addin with a DropDown with items showing as "Depart 1", "Depart 2" & "Depart 3" and one button to import from an Excel file.

The idea is user can select which department to prepare slides for. Then they can run the report which will copy the charts from the designated Excel file.

Below is example code to show what I am trying to do.
When I select department in the dropdown list; I get the message which department is selected from the DropDown. But when I tried to use that as a variable, my code did not recognise it.

XML code for Ribbon Addin:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"> 
   <ribbon> 
     <tabs> 
       <tab id="CustomTab" label="TestLabel"> 
        <group id="SampleGroup" label="BU Selection">    
        <dropDown id="DdropDown" label="Select Dept" onAction="DReportSelection" getSelectedItemIndex = "DropDown_OnGetSelectedItemIndex">
   <item id="item1" label="Depart 1" />
   <item id="item2" label="Depart 2" />
   <item id="item3" label="Depart 3" />
         </dropDown>
         </group > 
<group id="SampleGroup1" label="TestLabel"> 
           <button id="Button" label="Import Excel" imageMso="MicrosoftExcel" screentip="Import Data from Excel" size="large" onAction="ExcelImport" /> 
         </group > 
       </tab> 
     </tabs> 
   </ribbon> 
 </customUI>

VBA code in my presentation.

Sub BUReportSelection(control As IRibbonControl, ID As String, selectedindex As Variant)
    Dim department As String
    department = Choose(selectedindex + 1, "Depart 1", "Depart 2", "Depart 3")
    MsgBox department & " is selected ", vbInformation
End Sub

Sub ExcelImport()
    If department = "Depart 1" Then
        Call Macro4Department 1
    End If
    If department = "Depart 2" Then
        Call Macro4Department 2
    End If
    If department = "Depart 3" Then
        Call Macro4Department 3
    End If
End Sub

Note: code just to demonstrate.

Upvotes: 2

Views: 1329

Answers (1)

John Korchok
John Korchok

Reputation: 4913

VBA can't access the Ribbon directly. Your dropdown is triggering a callback to DReportSelection. Instead it should trigger a callback to a macro that stores the selected item (the id, not the label i.e. item1, item2, or item3) in a global variable. Then the DReportSelection macro (I assume that's what's in the second listing) can read that variable and call the relevant macro.

Upvotes: 3

Related Questions