View Full Version : Input box error handling
Shazam
05-26-2006, 08:49 AM
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
05-26-2006, 09:09 AM
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
05-26-2006, 11:02 PM
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
05-27-2006, 04:59 AM
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
05-27-2006, 06:11 AM
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
05-27-2006, 06:42 AM
Not a problem. Just change these two lines
Sheets(x & " Test").Activate
If ActiveSheet.Name = x & " Test" Then Exit Sub
Shazam
05-27-2006, 06:50 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.