Like with * returns incorrect sheet name

[Guest]

I have code to select worksheets using an inputbox box. I am using the code
below, but what is happening when someone puts in a name for a sheet that
can't be matched is it returns the first sheetname i.e. SHEET 1. What I would
like it to do is to return an error message. The code I have is as follows:

Private Sub CommandButton1_Click()
On Error GoTo ErrorHandler
Dim stclname As String
Dim stermes As String
Dim sh As Worksheet

Enterclname:
stclname = UCase(InputBox("Enter the Client Surname"))
If stclname = "" Then
Exit Sub
End If

For Each sh In ThisWorkbook.Worksheets
If UCase(sh.Name) Like stclname & "*" Then
sh.Activate
Exit Sub
End If

Next sh

Exit_CommandButton1_Click:
Exit Sub

ErrorHandler:
stermes = MsgBox("This worksheet doesn't exist." & Chr(13) & _
"Check the spelling")
Resume Enterclname

End Sub

 

[Norman Jones]

Hi Marianne,

Try:

'=============>>
Private Sub CommandButton1_Click()
Dim stclname As String
Dim sh As Worksheet

Do
stclname = UCase(InputBox("Enter the Client Surname"))
If stclname = "" Then
Exit Sub
End If

For Each sh In ThisWorkbook.Worksheets
If UCase(sh.Name) Like stclname & "*" Then
sh.Activate
Exit Sub
End If
Next sh

MsgBox ("This worksheet doesn't exist." & Chr(13) & _
"Check the spelling")
Loop

End Sub
'<<=============

 

[Tom Ogilvy]

Private Sub CommandButton1_Click()
On Error GoTo ErrorHandler
Dim stclname As String
Dim stermes As String
Dim sh As Worksheet

Enterclname:
stclname = UCase(InputBox("Enter the Client Surname"))
If stclname = "" Then
Exit Sub
End If
set sh = Worksheets(stclname)

sh.Activate
Exit Sub

ErrorHandler:
stermes = MsgBox( stclname & "doesn't exist." & Chr(13) & _
"Check the spelling")
Resume Enterclname

End Sub

 

[Guest]

Thank you very much. That works perfectly.

Marianne