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 ?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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
 
Upvote 0
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 ?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Did you see my suggestion in post#5, that doesn't need the Tri function?
 
Upvote 0
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 ?
 
Upvote 0
Are you adding sheets to the workbook whilst the userform is loaded?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top