Conditionally Clearing A Custom Ribbon Combobox

Tlev

New Member
Joined
Sep 22, 2009
Messages
3
Hi everyone,

I recently set up a custom Ribbon tab through customUI Editor for an oft-used workbook. Included in the tab is a combobox, the purpose of which is to be used as a navigational 'go to' tool. Its populated by the names of a large selection of worksheets and the onAction procedure activates whichever worksheet name is selected. This works fine.

Currently the worksheet name that was selected remains in the comboxbox afterwards, for the most part I am happy for it to do so with the exception of one instance: When one particular worksheet (a summary sheet) is activated, I want the combobox to be reset back to the default of no selection similar to when the file was first opened.

Despite experimenting with existing forum posts related to this top, e.g. invalidation of ribbon controls etc. I have been unable to get it working.

Can you guys please assist?


Here is the simplified version of my code with unrelated line items removed

XML:
<customui< font=""></customui<><customui< font=""><customui< font=""></customui<><customui< font=""> xmlns = "http://schemas.microsoft.com/office/2009/07/customui" on*Load=" rbx_on*Load ">
......
<combobox< font=""> </combobox<></customui<>
<customui< font=""><combobox< font=""></combobox<></customui<><customui< font=""><combobox< font=""><combobox< combobox<=""></combobox<></combobox<></customui<><customui< font=""><combobox< font=""></combobox<></customui<><customui< font=""><combobox< font=""> id ="Combobox 1" sizeString="W WWWWWWWWWWWWWWWWWWW"
getItemID ="cmb_getItemID"
getItemLabel ="cmb_getItemLabel"
getItemCount="cmb_itemC ount"
onChange="cmb_onChange" />
</combobox<>
</customui<>

</customui<>​


VBA:
Public grbxUI As IribbonUI
_________________________________________________

Public Sub rbx_on*Load (ribbon As IRibbonUI)
Set grbxUI = ribbon
End Sub
_________________________________________________

Sub cmb_getItemID (control As IRibbonControl, index As Integer , ByRef ID)
ID = index
End Sub
_________________________________________________

Sub cmb_getItemLabel (control As IRibbonControl, index As Integer , ByRef returne dVal)
Dim CC As Integer

CC = Sheets("Summary").Range("Sets").Rows.count
returnedVal = Sheets("Summary").Range("Sets").Cells(CC - index, 1)

End Sub
_________________________________________________

Sub cmb_itemCount (control As IRibbonControl, ByRef count)
count = Sheets("Summary").Range("Sets").count
End Sub
_________________________________________________

Sub cmb_onChange (control As IRibbonControl, text As String )

If text = "" Then
Exit Sub
Else
Sheets(text).Select
End If

End Sub

__________________________________________________

Sub RefreshRibbonCombobox1()
grbxUI.InvalidateControl ("Combobox1")
End Sub
_________________________________________________

Private Sub Worksheet_Activate () 'On the Summary Sheet

'I think I need code here to reset the combobox when only this particular worksheet is selected but everything I have tried doesnt work, just invalidating it only updates the dropdown list, but doesnt clear the item selected.

RefreshRibbonCombobox1
End sub

____________________________________________________


Any help someone can give me would be greatly appreciated.

Cheers
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Forum statistics

Threads
1,148,154
Messages
5,745,095
Members
423,922
Latest member
Taconumber

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