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 ?

 

[Fluff]

How about

Private Sub UserForm_Initialize()
   Dim i As Long
   ReDim Ary(1 To Sheets.Count)
   
   For i = 1 To Sheets.Count
      Ary(i) = Sheets(i).Name
   Next i
   Me.ComboBox1.List = Ary
   Me.ComboBox1.ListIndex = 0
End Sub

 

[Doflamingo]

Many thanks @Fluff that works

I had also that macro that allowed me to sort alphabetically the items of the combobox from A to Z

Here

Sub Tri(a, gauc, droi)          ' Quick sort
 ref = a((gauc + droi) \ 2)
 g = gauc: d = droi
 Do
     Do While a(g) < ref: g = g + 1: Loop
     Do While ref < a(d): d = d - 1: Loop
     If g <= d Then
       temp = a(g): a(g) = a(d): a(d) = temp
       g = g + 1: d = d - 1
     End If
 Loop While g <= d
 If g < droi Then Call Tri(a, g, droi)
 If gauc < d Then Call Tri(a, gauc, d)
End Sub

But that code does not work with the new sheet created, it worked only with the previous format that was Format(i, "Feuille000")

Any idea ?

 

[Doflamingo]

I have added it to the code you gave me but does not work to sort combobox items from A to Z -in red line-

Private Sub UserForm_Initialize()
   Dim i As Long
   ReDim Ary(1 To Sheets.Count)
   
   For i = 1 To Sheets.Count
      Ary(i) = Sheets(i).Name
   Next i
[COLOR=#ff0000]   Call Tri(Ary, 1, n)[/COLOR]
   Me.ComboBox1.List = Ary
   Me.ComboBox1.ListIndex = 0
End Sub

while with the previous 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)
  [COLOR=#ff0000]Call Tri(temp, 1, n)[/COLOR]
  Me.ComboBox1.List = temp
  Me.ComboBox1.ListIndex = 0
End Sub

 

[Fluff]

Ok, how about

Private Sub UserForm_Initialize()
   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
   Me.ComboBox1.List = Lst.toarray
   Me.ComboBox1.ListIndex = 0
End Sub

 

[Doflamingo]

I changed the variable ''temp'' by ''ary'' the new one you gave me in the macro that allows to sort from A to Z

Sub Tri(a, gauc, droi)          ' Quick sort
 ref = a((gauc + droi) \ 2)
 g = gauc: d = droi
 Do
     Do While a(g) < ref: g = g + 1: Loop
     Do While ref < a(d): d = d - 1: Loop
     If g <= d Then
     
[COLOR=#ff0000]       Ary = a(g): a(g) = a(d): a(d) = Ary[/COLOR]
       g = g + 1: d = d - 1
     End If
 Loop While g <= d
 If g < droi Then Call Tri(a, g, droi)
 If gauc < d Then Call Tri(a, gauc, d)
End Sub

But still does not work

 

[Fluff]

Did you see my suggestion in post#5, that doesn't need the Tri function?

 

[Doflamingo]

So sorry @Fluff, I had nos seen your answer in post#5, many thanks for your time and your help. That works perfectly

Just a last question, to see the new sheet added, at each time I have to reactivate the macro it means ''reset'' click on the little blue square in the VBA editor and then ''Run/sub Userform'' click on the little green triangle.

Do you have any idea how to skip that step and to see directly the new sheet added to the combobox of the userform when a new one is created ?

 

[Fluff]

Are you adding sheets to the workbook whilst the userform is loaded?

 

[Doflamingo]

Yes. I add new sheet while the userform is activated. I don't see the new sheet added immediately to the combobox of the usrform. To do so, I have to go back to the VBA editor, click on ''reset" -little blue square'' then on ''Run/sub Userform'' -little green triangle- and THEN I can see in the combobox the new sheet created previously in the workbook