Add automatically new sheet names to a combobox

Doflamingo

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,317
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
235
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
235
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
32,317
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
235
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
32,317
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
235
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
32,317
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
235
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
 

Forum statistics

Threads
1,082,298
Messages
5,364,378
Members
400,796
Latest member
vrcdesktop

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top