CustomUI Ribbon Function

BrianExcel

Well-known Member
Joined
Apr 21, 2010
Messages
975
Hopefully this is easier than I am making it.

I am creating a custom dropdown in the Excel Ribbon via CustomUI. The dropdown contains >50 items, manually added via the CustomUI XML code. This way, Excel doesn't need to search any sheets for the 50+ items when it loads, the values are just there by default.

The problem is, when I do that, the "fire" code that runs when the dropdown is changed no longer finds a value, so the macro I wrote based on that value is dead.

In order to prevent storing the list of 50+ items in an Excel sheet, I need to do 1 of 2 things:

1. Either manually load the Ribbon dropdown items via VBA upon workbook load (instead of loading via the XML)

OR

2. Tweak the existing macros to be able to recognize the pre-loaded XML value.

I will post a few snips of code trying to explain the order of operations...

Ribbon XML Code to load individual dropdown items (this part works fine to load the values):
VBA Code:
<dropDown id="dd1" onAction="DDOnAction" sizeString="My_Max_Length_String" getSelectedItemIndex="DDItemSelectedIndex">
                <item id="item_Allen" label="Allen" />
                <item id="item_Alpharetta" label="Alpharetta" />
                <item id="item_Auburn_Hills" label="Auburn Hills" />
                <item id="item_Austin" label="Austin" />
            </dropDown>

Ribbon VBA Code to Look at a Range and add the values to the Drop Down Box
*** This was the old way I was adding items to the drop down but I don't like having to maintain a vulnerable sheet where values are stored. I want the values added via VBA code OR XML code...


Code:
''Callback for Dropdown getItemCount.
''Tells Excel how many items in the drop down.
Sub DDItemCount(control As IRibbonControl, ByRef returnedVal)
    With Sheet1.Range("B2:B100")
        Set ListItemsRg = Range(.Cells(1), .Offset(.Rows.Count).End(xlUp))
        ItemCount = ListItemsRg.Rows.Count
        returnedVal = ItemCount
        
        MsgBox returnedVal
    End With
End Sub

Callback called once for each item in drop down

Code:
''Callback for dropdown getItemLabel.
''Called once for each item in drop down.
''If DDItemCount tells Excel there are 10 items in the drop down
''Excel calls this sub 10 times with an increased "index" argument each time.
''We use "index" to know which item to return to Excel.
Sub DDListItem(control As IRibbonControl, index As Integer, ByRef returnedVal)
    returnedVal = ListItemsRg.Cells(index + 1).Value
    ''index is 0-based, our list is 1-based so we add 1.
End Sub

Drop down change handler.
Code:
''Drop down change handler.
''Called when a drop down item is selected.

''Returns index of item to display.

Sub DDItemSelectedIndex(control As IRibbonControl, ByRef returnedVal)
    returnedVal = 0
    MySelectedItem = ListItemsRg.Cells(1).Value
End Sub

Drop Down Action
Code:
Sub DDOnAction(control As IRibbonControl, ID As String, index As Integer)
Dim r As Range, rAll As Range
Dim iLastRow As Integer
Dim VenueName As String

MySelectedItem = ListItemsRg.Cells(index + 1).Value
                
VenueName = MySelectedItem

Dim dic As Object

Set dic = CreateObject("scripting.dictionary")


Again - essentially, I need a drop down with pre-populated values in the Excel Ribbon upon opening, I need those values to be able to be added/deleted/modifiable via VBA or XLM, and finally, I need the drop down action (selecting a value) to trigger working code.

I am open to how all this gets done.

THanks for any help!
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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