Causing out of range error - 9 (1 Viewer)

raghuprabhu

Registered User.
Local time
Today, 02:34
Joined
Mar 24, 2008
Messages
154
Hello Everyone,

I am trying to use an Input Box to prompt member to input a 7 digit number starting with 6, for example, “6014342” to select the worksheet to print. I want to trap error if the number is not 7 digits long or does not start with a “6” or is a blank or an alphabet string.
The following is causing out of range error 9

Private Sub WorkSheetName_Click()
Dim SheetName As String
SheetName = Application.InputBox("Enter a sheet number")
If len(WorkSheetName) <> 7 then
Msgbox ”The number should be 7 digits long!”
Elseif left(WorksheetName, 1) <> 6 then
Msgbox ”The number should begin with 6!”
End if
Sheets(SheetName).PrintOut
MsgBox "Sheet for " & SheetName & " --- Printed!"
End Sub

Please help

Thank you
Raghu
 

moke123

AWF VIP
Local time
Today, 05:34
Joined
Jan 11, 2013
Messages
3,852
It appears that if either of your conditions are true you are getting your message box however your code will still continue. Have you tried putting an exit sub after your msgbox call to stop your code from continuing?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:34
Joined
May 7, 2009
Messages
19,175
you have variable Sheetname, yet you use WorksheetName

Private Sub WorkSheetName_Click()
Dim SheetName As String
SheetName = Application.InputBox("Enter a sheet number")
If len(SheetName) <> 7 then
Msgbox ”The number should be 7 digits long!”
Elseif left(SheetName, 1) <> 6 then
Msgbox ”The number should begin with 6!”
Else
Sheets(SheetName).PrintOut
MsgBox "Sheet for " & SheetName & " --- Printed!"
End if
End Sub
 

raghuprabhu

Registered User.
Local time
Today, 02:34
Joined
Mar 24, 2008
Messages
154
I realised...and fixed and working now thank you.

'https://www.thespreadsheetguru.com/the-code-vault/2015/3/26/loop-input-box-until-receives-correct-valid-entry

Code:
Sub LoopInput_Box()

Dim InputQuestion As String
Dim myAnswer As String

InputQuestion = "Please enter the Pmkeys Number to print that report."

Do
    myAnswer = Application.InputBox(InputQuestion, "EID Number", Type:=1)
        If TypeName(myAnswer) = "Boolean" Then Exit Sub
Loop While Len(myAnswer) <> 7 Or Left(myAnswer, 1) <> 6
        
        Sheets(myAnswer).PrintOut
        MsgBox "Sheet for " & myAnswer & " --- Printed!"

End Sub
 
Last edited:

Users who are viewing this thread

Top Bottom