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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,956
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,956
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,956
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,956
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,872
Messages
5,489,430
Members
407,688
Latest member
Jerry1383320

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top