Private Sub REFRESH_COMBOBOX1()
Dim ws As Worksheet
ComboBox1.Clear
For Each ws In ThisWorkbook.Sheets
If ws.Visible Then ComboBox1.AddItem(ws.Name)
Next ws
End Sub


Private Sub ComboBox1_DropButtonClick()
REFRESH_COMBOBOX1
End Sub

Sub REFRESH_COMBOBOX1()
Dim ws As Worksheet
Dim i As Long
Do While ComboBox1.ListCount > 0
    ComboBox1.RemoveItem (0)
Loop
For Each ws In ThisWorkbook.Sheets
    If ws.Visible Then ComboBox1.AddItem (ws.Name)
Next ws
End Sub

Call RefreshSheetList()

Private Sub RefreshSheetList()
With ComboBox1
    .Clear
    For n=1 To ActiveWorkbook.Sheets.Count
        .AddItem ActiveWorkbook.Sheets(n).Name
    Next n
End With
End Sub

Sub CommandButton1_Click()
Dim w as Worksheet
With ActiveWorkbook
    Set w = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
    w.Name = Me.TextBox1.Text
End With
Call RefreshSheetList()
End Sub

Private Sub REFRESH_COMBOBOX1() ' Routine for refreshing the list of names in box

    Dim ws As Worksheet     ' need a worksheet variable
    ComboBox1.Clear         ' clear the current list

  ' Cycle through every worksheet in the book, if it is NOT hidden then add name to list
    For Each ws In ThisWorkbook.Sheets
        If ws.Visible Then ComboBox1.AddItem (ws.Name)
    Next ws
End Sub '


' Separate routine to register the user putting focus on the box
' This happens every time the user clicks on or enters ComboBox1
Private Sub ComboBox1_Enter()
    REFRESH_COMBOBOX1 ' Calling the routine above
End Sub