Populate ComboBox With Sheet Names Dynamically

Question

I am having trouble with populating a combo box on a excel ribbon dynamically. I wish for the combo box to be populated using the names of the sheets of the workbook dynamically. I am able to select the sheet names already presentin the combo box that is placed on the ribbon however I do not seam to be able to code the VBA to populate the combo box with the sheet names if I add them or modify the name.

I have written below code but its not working :

Sub SelectionFeuille_GetItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)


 Dim dTime As Date

dTime = Now + TimeValue("00:00:01") 'hh:mm:ss
Application.OnTime dTime, "Refresh_all"

returnedVal = ActiveWorkbook.Worksheets(index + 1).Name

End Sub

Please help me....

Possible duplicate of AutoRefresh Combobox in excel ribbon bar — Rory
maybe i was not clear to all at the first time, so tried so explain in a better way by explaining in a new question — Rishav Tripathi

Ambie Ambie · Accepted Answer · 2015-12-24T14:23:07

The simplest way I've found to do this is to capture the Calculate event, and I do that by having a hidden worksheet with formulae to each sheet in its cells. It's far from perfect and, if truth be told, is a pretty ugly workaround, but at least it's food for thought for you. I guess a timer would also work but that seems just as ugly.

All of this code goes in the code behind your workbook:

Option Explicit
Private Const NAMES_SHEET As String = "Hidden|Sheet|Names"
Private mNamesSheet As Worksheet
Private Sub Workbook_Open()
    Dim b As Boolean

    b = Application.ScreenUpdating
    On Error Resume Next
    Set mNamesSheet = ThisWorkbook.Worksheets(NAMES_SHEET)
    On Error GoTo 0
    If mNamesSheet Is Nothing Then
        Application.ScreenUpdating = False
        Set mNamesSheet = ThisWorkbook.Worksheets.Add
        mNamesSheet.Name = NAMES_SHEET
        mNamesSheet.Visible = xlSheetVeryHidden
    End If
    WriteNamesOfSheets
    Application.ScreenUpdating = b

End Sub
Private Sub WriteNamesOfSheets()
    Dim v() As Variant
    Dim ws As Worksheet
    Dim i As Integer
    Dim b As Boolean

    b = Application.EnableEvents
    Application.EnableEvents = False
    ReDim v(1 To ThisWorkbook.Worksheets.Count, 1 To 1)
    mNamesSheet.Cells.Clear
    i = 0
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            i = i + 1
            v(i, 1) = "=" & ws.Name & "!A1"
        End If
    Next

    mNamesSheet.Range("A1").Resize(UBound(v, 1)).Formula = v
    Application.EnableEvents = b
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Dim ws As Worksheet
    Dim b As Boolean

    On Error GoTo EH
    b = Application.EnableEvents
    Application.EnableEvents = False
    WriteNamesOfSheets
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            '
            'Populate your combobox here with ws.Name
            '
        End If
    Next

    Application.EnableEvents = b

    Exit Sub

EH:
    Err.Clear
End Sub

Populate ComboBox With Sheet Names Dynamically

1 Answers