Like with * returns incorrect sheet name
 

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

Like with * returns incorrect sheet name
 

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
'<<=============


---
Regards,
Norman


"marianne" wrote in message
...
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

Like with * returns incorrect sheet name
 

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

--
regards,
Tom Ogilvy


"marianne" wrote in message
...
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

Like with * returns incorrect sheet name
 

Thank you very much. That works perfectly.

Marianne

"Norman Jones" wrote:

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
'<<=============


---
Regards,
Norman


"marianne" wrote in message
...
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