Trying to fix a Printing Macro

[Almamba]

Hi Everyone
I have been trying to fix the following code whose problem apparently is on
the statement: Sheets(Sheetname).Select that comes after
Sheetname=InputBox("Enter sheet to print", "Print a financial Report.")
When I remove Sheets(Sheetname).Select, the Documentation sheet is printed.
When I leave it, the Microsoft Visual Basic dialog box displays the message:
Run-time error '9'
Subscript out of range

I will appreciate it if anybody help me to fix this macro. Thank you.

Sub Print_Macro()
Application.ScreenUpdating = False
' Screen updating for application object is now off
' Print_Macro Macro
'
' This macro displays an input box and prompts the user for the name of the
financial
' report to print. The macro then prints the selected report and reselects
the Documentation worksheet.
' The macro also verifies that the user has entered one of the financial
worksheets and not anything else.


If Sheetname = "Finance" Or Sheetname = "Income" Or Sheetname = "Balance" Then
Sheets(Sheetname).Select
ElseIf Sheetname <> "" Then
MsgBox "Please type Finance, Income, or Balance", vbExclamation,
"Invalid Sheet Name"
End If

Sheetname = InputBox("Enter sheet to print.", "Print a financial report.")
Sheets(Sheetname).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Documentation").Select
Range("A1:B1").Select
Application.ScreenUpdating = True
' Screen updating for application object is now on

End Sub

 

[Patrick Bielen]

Hi Almamba,

Run-time error '9'
Subscript out of range

Macro runs fine here, you only get Subscript out of range
when you enter a Name that does not exists.

You can fix it this way... put the next code after the inputbox...

Sheetname = InputBox("Enter sheet to print.", "Print a financial report.")
On Error Resume Next
Sheets(Sheetname).Select
If Err = 9 Then
MsgBox "Seems the entered sheet does not exists !" & vbCrLf & _
"Please type Finance, Income, or Balance", vbExclamation, "Invalid Sheet
Name"
Exit Sub
End If

Best Regards,

Patrick Bielen
MCP / SCJP