Add automatically new sheet names to a combobox
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

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

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

    Default Re: Add automatically new sheet names to a combobox

    @Fluff

    Actually the new sheets created are added automatically to the combobox if I close the userform and anyway it reappears on any sheets thanks to those lines of code

    Code:
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    F_Onglets.Show
    End Sub
    
    
    
    
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    F_Onglets.Show
    End Sub

  2. #12
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,976
    Post Thanks / Like
    Mentioned
    460 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Add automatically new sheet names to a combobox

    You can get the new sheets added when created like
    Code:
    Private Sub CommandButton1_Click()
    Sheets.Add(, Sheets(Sheets.Count)).Name = "HELP!"
    Me.ComboBox1.List = GetList
    End Sub
    
    
    Private Sub UserForm_Initialize()
       Me.ComboBox1.List = GetList
       Me.ComboBox1.ListIndex = 0
    End Sub
    Private Function GetList() As Variant
       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
       GetList = Lst.toarray
    End 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

  3. #13
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,519
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Add automatically new sheet names to a combobox

    You could use code like this and call AddSheetName explicitly when the new sheets are added.
    Code:
    Private Sub UserForm_Initialize()
        Dim oneSheet As Worksheet
        For Each oneSheet In ThisWorkbook.Sheets
            Me.AddSheetName oneSheet.Name
        Next oneSheet
    End Sub
    
    Public Sub AddSheetName(NameToAdd As String)
        Dim i As Long
        With ListBox1
            For i = 0 To .ListCount - 1
                If LCase(NameToAdd) < LCase(.List(i)) Then
                    Exit For
                ElseIf LCase(NameToAdd) = LCase(.List(i)) Then
                    Exit Sub
                End If
            Next i
            .AddItem NameToAdd, i
            For i = 0 To .ListCount - 1
                .Selected(i) = (.List(i) = ActiveSheet.Name)
            Next i
        End With
    End Sub

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

    Default Re: Add automatically new sheet names to a combobox

    Hello @Fluff and @mikerickson many thanks for your answer, help and time on that matter.

    That replies to my question.

    Kind regards

  5. #15
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,976
    Post Thanks / Like
    Mentioned
    460 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Add automatically new sheet names to a combobox

    Glad we could help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •