Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Add automatically new sheet names to a combobox
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2019
    Posts
    213
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Add automatically new sheet names to a combobox

    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 ?

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Add automatically new sheet names to a combobox

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Apr 2019
    Posts
    213
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add automatically new sheet names to a combobox

    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 ?

  4. #4
    Board Regular
    Join Date
    Apr 2019
    Posts
    213
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add automatically new sheet names to a combobox

    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
       Call Tri(Ary, 1, n)
       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)
      Call Tri(temp, 1, n)
      Me.ComboBox1.List = temp
      Me.ComboBox1.ListIndex = 0
    End Sub

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Add automatically new sheet names to a combobox

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    Board Regular
    Join Date
    Apr 2019
    Posts
    213
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add automatically new sheet names to a combobox

    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
         
           Ary = a(g): a(g) = a(d): a(d) = Ary
           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

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Add automatically new sheet names to a combobox

    Did you see my suggestion in post#5, that doesn't need the Tri function?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    Board Regular
    Join Date
    Apr 2019
    Posts
    213
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add automatically new sheet names to a combobox

    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 ?

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Add automatically new sheet names to a combobox

    Are you adding sheets to the workbook whilst the userform is loaded?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular
    Join Date
    Apr 2019
    Posts
    213
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add automatically new sheet names to a combobox

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •