Add automatically new sheet names to a combobox

Doflamingo

Board Regular
Joined
Apr 16, 2019
Messages
238
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

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

Code:
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 ?
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,721
Office Version
365
Platform
Windows
How about
Code:
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

Board Regular
Joined
Apr 16, 2019
Messages
238
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

Code:
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

Board Regular
Joined
Apr 16, 2019
Messages
238
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-

Code:
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

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,721
Office Version
365
Platform
Windows
Ok, how about
Code:
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

Board Regular
Joined
Apr 16, 2019
Messages
238
I changed the variable ''temp'' by ''ary'' the new one you gave me in the macro that allows to sort from A to Z

Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,721
Office Version
365
Platform
Windows
Did you see my suggestion in post#5, that doesn't need the Tri function?
 

Doflamingo

Board Regular
Joined
Apr 16, 2019
Messages
238
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,721
Office Version
365
Platform
Windows
Are you adding sheets to the workbook whilst the userform is loaded?
 

Doflamingo

Board Regular
Joined
Apr 16, 2019
Messages
238
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,446
Messages
5,486,947
Members
407,572
Latest member
smcexcel

This Week's Hot Topics

Top