Input box error handling

[Shazam]

How to put a msg box if the user type the wrong worksheet tab name a msg box pops up. Now if they hit cancel then it will exit the macro.

This is what I have so far.





Sub Find_Tabs()
Dim x As String
On Error Resume Next
x = Application.InputBox("Please enter your worksheet name:", "Name")
If x = "" Then
Exit Sub
End If
Sheets(x).Select

End Sub

[mdmackillop]

You can allow them to retry as follows

Option Explicit
Sub Find_Tabs()
Dim x As String
TryAgain:
On Error GoTo ErrH
x = Application.InputBox("Please enter your worksheet name:", "Name")
If x = "" Then
Exit Sub
End If
Sheets(x).Select
Exit Sub
ErrH:
MsgBox "No such sheet!"
GoTo TryAgain
End Sub

[Shazam]

Hi mdmackillop,


I tried your code and its not quite right. I'm looking for if user types in a worksheet name that it does not exist a message box pops up saying " Worksheet name does not exist, try again" But if it does then run macro. Also if user hits the cancel button then it will exit sub. What do you think?

[mdmackillop]

Hi Shazam,
A bit of a problem getting the loop to work properly, but try this.

Option Explicit
Option Compare Text
Sub Find_Tabs()
Dim x As Variant
TryAgain:
x = Application.InputBox("Please enter your worksheet name:", "Name")
If x = "" Or x = False Then Exit Sub
On Error Resume Next
Sheets(x).Activate
If ActiveSheet.Name = x Then Exit Sub
On Error GoTo 0
If MsgBox("Worksheet name does not exist, try again", vbOKCancel) _
= vbCancel Then Exit Sub
GoTo TryAgain
End Sub

[Shazam]

Awesome!!

One more thing. The user have over 100 worksheets. All different names but all end of saying test. Example


256 Test
859 Test
87459 Test
10 Test
1 Test
RGA Test
Cast Test

So instead of the user typing the whole name of the worksheet the user will only type:

256
859
87459
10
1
RGA
Cast

In the inputbox. Can the code do some kind of Left statement?

[mdmackillop]

Not a problem. Just change these two lines

Sheets(x & " Test").Activate
If ActiveSheet.Name = x & " Test" Then Exit Sub

[Shazam]

Thank You so much this have been a good learning process.:hi:



Option Explicit
Option Compare Text
Sub Find_Tabs()
Dim x As Variant
TryAgain:
x = Application.InputBox("Please enter your worksheet name:", "Name")
If x = "" Or x = False Then Exit Sub
On Error Resume Next
Sheets(x & " Test").Activate
If ActiveSheet.Name = x & " Test" Then Exit Sub
On Error GoTo 0
If MsgBox("Worksheet name does not exist, try again", vbOKCancel) _
= vbCancel Then Exit Sub
GoTo TryAgain
End Sub