Add automatically new sheet names to a combobox

[Doflamingo]

Hi all,

Imagine I have a combobox from a userform that allows me to navigate through a workbook with many sheets -dozens of dozens-

I would like to know why the code of the userform initialize does not add the name of the sheet to the combobox when a new one is created

Current code

Private Sub UserForm_Initialize()
 For Each s In ActiveWorkbook.Sheets
    Me.ComboBox1.AddItem s.Name
  Next s
  Dim temp()
  For i = 1 To Sheets.Count
    ReDim Preserve temp(1 To i)
    temp(i) = Sheets(i).Name
  Next i
  n = UBound(temp)
  Call Tri(temp, 1, n)
  Me.ComboBox1.List = temp
  Me.ComboBox1.ListIndex = 0
End Sub

I have a macro

Sub essai()
  For i = 2 To Sheets.Count
   Sheets(i).Name = Format(i, "Feuille000")
  Next i
End Sub

And the new sheet created appear in the listbox only if it follows that format Feuille002 or Feuille003 etc.

But I would like if I create a sheet called ''blabla '' appears in the combobox also ... because currently it does not work if I don't follow the specific format above


Any idea ?

 

[Doflamingo]

@Fluff

Actually the new sheets created are added automatically to the combobox if I close the userform and anyway it reappears on any sheets thanks to those lines of code

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
F_Onglets.Show
End Sub




Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
F_Onglets.Show
End Sub

 

[Fluff]

You can get the new sheets added when created like

Private Sub CommandButton1_Click()
Sheets.Add(, Sheets(Sheets.Count)).Name = "HELP!"
Me.ComboBox1.List = GetList
End Sub


Private Sub UserForm_Initialize()
   Me.ComboBox1.List = GetList
   Me.ComboBox1.ListIndex = 0
End Sub
Private Function GetList() As Variant
   Dim i As Long
   Dim Lst As Object

   Set Lst = CreateObject("system.collections.arraylist")
   For i = 1 To Sheets.Count
      Lst.Add Sheets(i).Name
   Next i
   Lst.Sort
   GetList = Lst.toarray
End Function

 

[mikerickson]

You could use code like this and call AddSheetName explicitly when the new sheets are added.

Private Sub UserForm_Initialize()
    Dim oneSheet As Worksheet
    For Each oneSheet In ThisWorkbook.Sheets
        Me.AddSheetName oneSheet.Name
    Next oneSheet
End Sub

Public Sub AddSheetName(NameToAdd As String)
    Dim i As Long
    With ListBox1
        For i = 0 To .ListCount - 1
            If LCase(NameToAdd) < LCase(.List(i)) Then
                Exit For
            ElseIf LCase(NameToAdd) = LCase(.List(i)) Then
                Exit Sub
            End If
        Next i
        .AddItem NameToAdd, i
        For i = 0 To .ListCount - 1
            .Selected(i) = (.List(i) = ActiveSheet.Name)
        Next i
    End With
End Sub

 

[Doflamingo]

Hello @Fluff and @mikerickson many thanks for your answer, help and time on that matter.

That replies to my question.

Kind regards

 

[Fluff]

Glad we could help & thanks for the feedback