Option Explicit
Dim tablo()
Private Sub ComboBox1_Change()
Dim mot$, i&
If ComboBox1.Value = "" Then ComboBox1.Clear: Exit Sub
With ComboBox1
mot = .Value
.Clear
On Error Resume Next
For i = 1 To UBound(tablo)
If Left(LCase(tablo(i)), Len(.Value)) = .Value Then .AddItem tablo(i)
Next
.Value = mot
If .ListCount = 1 Then
.Enabled = False: .Enabled = True: .DropDown
If .ListIndex > -1 Then Sheets(.List(.ListIndex)).Select
Else
.ListRows = .ListCount
.DropDown
End If
If .ListCount = 0 Then .Value = Left(.Value, Len(.Value) - 1)
.Enabled = False: .Enabled = True: .DropDown
End With
Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_Activate()
Dim i As Integer, X&
Me.ComboBox1.Clear
ComboBox1.Value = ""
ReDim tablo(1 To Sheets.Count - 1)
For i = 1 To Sheets.Count
If Sheets(i).Name <> "Menu" Then X = X + 1: tablo(X) = Sheets(i).Name
Next
'Me.ComboBox1.List = tablo
End Sub