Show some sheets in Custom xml ribbon combobox

[tiredofit]

I have a custom ribbon with a combobox.

This combobox shows the names of all the worksheets within my workbook.

I have taken the code from here:

https://stackoverflow.com/questions/61824181/fill-in-a-custom-ribbon-combobox-runtime

xml code

<!--RibbonX Visual Designer 2.33 for Microsoft Excel CustomUI14 . XML Code produced on 2020/05/16-->
<customUI
    xmlns = "http://schemas.microsoft.com/office/2009/07/customui"
    onLoad="Initialize">
    <ribbon >
        <tabs >
            <tab
                id = "Tab1"
                label="Tab1">
                <group
                    id = "Group1"
                    label="Group1">
                    <comboBox
                        id = "Combo3"
                        Label = "MyCombobox"
                        getItemCount = "Combo3_getItemCount"
                        getItemID = "Combo3_getItemID"
                        getItemLabel = "Combo3_getItemLabel"
                        getText = "Combo3_getText"
                        onChange="Combo3_onChange"/>
                </group >
            </tab >
        </tabs >
    </ribbon >
</customUI >

vba code

Dim myRibbon As IRibbonUI

'Callback for customUI.onLoad
Sub Initialize(ribbon As IRibbonUI)
    Set myRibbon = ribbon
End Sub

'Callback for Combo3 getItemCount (called once when the combobox is invalidated)
Sub Combo3_getItemCount(control As IRibbonControl, ByRef returnedVal)
    returnedVal = 10 'the number of items for combobox
End Sub

'Callback for Combo3 getItemID (called 10 times when combobox is invalidated)
Public Sub Combo3_getItemID(control As IRibbonControl, index As Integer, ByRef id)
    id = "ComboboxItem" & index + 1
End Sub

'Callback for Combo3 getItemLabel (called 10 times when combobox is invalidated)
Sub Combo3_getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
    returnedVal = "Item" & index + 1
End Sub

'Callback for Combo3 getText
Sub Combo3_getText(control As IRibbonControl, ByRef returnedVal)
    returnedVal = "" 'clears the text from the combobox
End Sub

'Callback for Combo3 onChange
Sub Combo3_onChange(control As IRibbonControl, text As String)
    MsgBox "You have chosen " & text
End Sub

Sub UpdateCombo3()
    myRibbon.InvalidateControl "Combo3" 'invalidates the cache for the combobox
End Sub

I want a slight modification.

Instead of displaying every sheet name in the combobox, I would like to have one sheet (called Secret) NOT to be displayed in the combobox.

I tried manually moving Secret to be the first sheet (or the last) and amended this Sub:

'Callback for Combo3 getItemLabel (called 10 times when combobox is invalidated)
Sub Combo3_getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
    If index =0 then
        returnedVal = "Item" & index + 2
    Else   
        returnedVal = "Item" & index + 1
    End If
End Sub

but I didn't achieve the desired result.

Is it possible to only show some of the sheets?

Thanks

 

[tiredofit]

I've worked it out.

I manually moved the sheet Secret to be the first sheet, then amended the code to this:

'Callback for Combo3 getItemLabel (called 10 times when combobox is invalidated)
Sub Combo3_getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
        returnedVal = "Item" & index + 2
   
End Sub

 

[tiredofit]

Worked out an even better solution:

Dim Flag As Boolean ' ADDED THIS

Dim myRibbon As IRibbonUI

'Callback for customUI.onLoad
Sub Initialize(ribbon As IRibbonUI)
    Set myRibbon = ribbon
End Sub

'Callback for Combo3 getItemCount (called once when the combobox is invalidated)
Sub Combo3_getItemCount(control As IRibbonControl, ByRef returnedVal)
   
    Flag = False ' ADDED THIS

    returnedVal = 10 'the number of items for combobox
End Sub

'Callback for Combo3 getItemID (called 10 times when combobox is invalidated)
Public Sub Combo3_getItemID(control As IRibbonControl, index As Integer, ByRef id)
    id = "ComboboxItem" & index + 1
End Sub


' CHANGED THIS

'Callback for Combo3 getItemLabel (called 10 times when combobox is invalidated)
Sub Combo3_getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)

Select Case Worksheets(index + 1).Name
        Case "Secret"
            returnedVal = Worksheets(index + 2).Name
            SheetFlag = True
        Case Else
            Select Case SheetFlag
                Case True
                    returnedVal = Worksheets(index + 2).Name
                Case False
                    returnedVal = Worksheets(index + 1).Name           
            End Select
    End Select
End Sub

'Callback for Combo3 getText
Sub Combo3_getText(control As IRibbonControl, ByRef returnedVal)
    returnedVal = "" 'clears the text from the combobox
End Sub

'Callback for Combo3 onChange
Sub Combo3_onChange(control As IRibbonControl, text As String)
    MsgBox "You have chosen " & text
End Sub

Sub UpdateCombo3()
    myRibbon.InvalidateControl "Combo3" 'invalidates the cache for the combobox
End Sub

The advantage of this solution is the worksheet Secret can be anywhere, not neccessarily the first or last sheet in the Worksheets collection.