Option Explicit
Sub SingleState()
Dim oExcelList As Object
Dim oWbk As Excel.Workbook
Dim oWsh As Excel.Worksheet
Dim strName As String
Set oExcelList = CreateObject("System.Collections.ArrayList")
Set oWbk = ThisWorkbook
With oWbk
For Each oWsh In .Sheets
If oWsh.Name Like "Summe_?" Then oExcelList.Add oWsh.Name
Next oWsh
oExcelList.Sort
On Error GoTo noInput
strName = InputBox(Join(oExcelList.toarray(), Chr(10)), "Nummer zur Summe eingeben")
strName = Replace("Summe_?", "?", strName)
Set oWsh = .Sheets(strName)
On Error GoTo 0
oWsh.Activate
End With
noInput:
Set oExcelList = Nothing
Set oWbk = Nothing
End Sub
Sub SeveralState()
Dim oWbk As Excel.Workbook
Dim oWsh As Excel.Worksheet
Dim strName As String
Set oWbk = ThisWorkbook
With oWbk
For Each oWsh In .Sheets
If oWsh.Name Like "Summe_?" Then
On Error GoTo aflop
oWsh.Activate
Call MsgBox("Mach was", vbInformation, oWsh.Name & " aktiv")
On Error GoTo 0
End If
Next oWsh
End With
aflop:
Set oWbk = Nothing
End Sub