Remembering the last used dropdown menu in a custom ribbon when a file is closed

[Bassie]

Hi,

I recently started a project in which I am creating a custom excel ribbon for myself. One of my tools has to do with a dropdown button within the ribbon. The dropdown is working fine but I would love excel to remember the ID the dropdown was on when excel is closed and put it on the same ID when I open excel again. I know this is possible as I have seen it before but can figure out for the life of me how.

Thanks in advanced,
Bassie

 

[Domenic]

When a selection is made from your dropdown, store its index in a location, such as a worksheet cell, defined name, or registry setting. Then, when the workbook is opened, retrieve it from that location.

So, for example, let's say that we want to store the index in Sheet1!A1, and that the id for your dropdown is DropDown1. First, add the getSelectedItemIndex callback to your XML code. This will trigger its VBA callback after you open your workbook and click on the relevant tab. Then, if you haven't already done so, add the onChange callback, which will trigger its VBA callback and store the index for the selected item. So your XML code might look something like this...

                    <dropDown
                        id="Dropdown1"
                        label="LabelName"
                        getItemCount="Dropdown1_getItemCount"
                        getItemID="Dropdown1_getItemID"
                        getItemLabel="Dropdown1_getItemLabel"
                        getSelectedItemIndex="Dropdown1_getSelectedItemIndex"
                        onAction="Dropdown1_OnChange"/>

Then, the VBA callbacks would be...

Public Sub Dropdown1_getSelectedItemIndex(control As IRibbonControl, ByRef returnedVal)

    Dim selectedItemIndex As Long
    selectedItemIndex = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value
    
    returnedVal = selectedItemIndex
    
End Sub

Public Sub Dropdown1_OnChange(control As IRibbonControl, id As String, index As Integer)
    
    ThisWorkbook.Worksheets("Sheet1").Range("A1").Value = index
    
    'etc
    '
    '
    
End Sub

Hope this helps!

 

[Bassie]

When a selection is made from your dropdown, store its index in a location, such as a worksheet cell, defined name, or registry setting. Then, when the workbook is opened, retrieve it from that location.

So, for example, let's say that we want to store the index in Sheet1!A1, and that the id for your dropdown is DropDown1. First, add the getSelectedItemIndex callback to your XML code. This will trigger its VBA callback after you open your workbook and click on the relevant tab. Then, if you haven't already done so, add the onChange callback, which will trigger its VBA callback and store the index for the selected item. So your XML code might look something like this...

                    <dropDown
                        id="Dropdown1"
                        label="LabelName"
                        getItemCount="Dropdown1_getItemCount"
                        getItemID="Dropdown1_getItemID"
                        getItemLabel="Dropdown1_getItemLabel"
                        getSelectedItemIndex="Dropdown1_getSelectedItemIndex"
                        onAction="Dropdown1_OnChange"/>

Then, the VBA callbacks would be...

Public Sub Dropdown1_getSelectedItemIndex(control As IRibbonControl, ByRef returnedVal)

    Dim selectedItemIndex As Long
    selectedItemIndex = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value
   
    returnedVal = selectedItemIndex
   
End Sub

Public Sub Dropdown1_OnChange(control As IRibbonControl, id As String, index As Integer)
   
    ThisWorkbook.Worksheets("Sheet1").Range("A1").Value = index
   
    'etc
    '
    '
   
End Sub

Hope this helps!

Hi,

Thanks a lot for your clear explenation you mentioned we can store the last selected dropdown ID in a worksheet cell, defined name, or registry setting. What do you think is the best option if this add-in is going to be shared to multiple people and used on all excel workbooks?

Thanks again!

 

[Domenic]

I would simply store it in a worksheet cell within your add-in.

Cheers!

 

[Bassie]

I would simply store it in a worksheet cell within your add-in.

Cheers!

Great, thanks a lot again! :D