Error Handling
 

Hello. Thanks to the help of many of you, I have the procedure below. Can
someone help with error handling? The code prints out sheets within a
file - both of which are determined from cell values within a spreadsheet.
I would like to capture the error if excel opens a book and tries to print a
sheet that does not exist. Thanks in advance!

datapath = ThisWorkbook.ActiveSheet.Range("A3").Value
Set pr = ThisWorkbook.ActiveSheet.Cells(ActiveCell.Row, 3)
If Trim(pr.Value) < "" Then
WbOpen = datapath & pr.Value
Set bk = Workbooks.Open(WbOpen)
Set rng1 = pr.Offset(0, 1).Resize(1, 50).SpecialCells(xlConstants)
For Each cell1 In rng1
If LCase(cell1.Value) = "all" Then
bk.PrintOut
Cells(pr.Row, 3).Interior.ColorIndex = 35
Exit For
Else
If Trim(cell1.Value) < "" Then
bk.Worksheets(cell1.Value).PrintOut
End If
End If
Next
Cells(pr.Row, 3).Interior.ColorIndex = 35
End If
WbOpen = ""
bk.Close SaveChanges:=False

Error Handling
 

hi,
selecting a sheet that does not exits is error 9:subscript out of range

at the top of your code add this...

On Error GoTo ErrorHandler

at the end of your code add something like this....

mysub_exit:
exit sub
ErrorHandler:
' you can Display error information.
MsgBox "Error number " & Err.Number & ": " & Err.Description
' or Resume with statement following occurrence of error.
If Err.Number = 9 Then
MsgBox ("Sorry, that sheet doesn't exist. Try Again")
Resume Mysub_Exit
End If
end sub

There are other way to handle errors but this will get you started.
in xl help type error handler for more info.

regards
FSt1


"Steph" wrote:

Hello. Thanks to the help of many of you, I have the procedure below. Can
someone help with error handling? The code prints out sheets within a
file - both of which are determined from cell values within a spreadsheet.
I would like to capture the error if excel opens a book and tries to print a
sheet that does not exist. Thanks in advance!

datapath = ThisWorkbook.ActiveSheet.Range("A3").Value
Set pr = ThisWorkbook.ActiveSheet.Cells(ActiveCell.Row, 3)
If Trim(pr.Value) < "" Then
WbOpen = datapath & pr.Value
Set bk = Workbooks.Open(WbOpen)
Set rng1 = pr.Offset(0, 1).Resize(1, 50).SpecialCells(xlConstants)
For Each cell1 In rng1
If LCase(cell1.Value) = "all" Then
bk.PrintOut
Cells(pr.Row, 3).Interior.ColorIndex = 35
Exit For
Else
If Trim(cell1.Value) < "" Then
bk.Worksheets(cell1.Value).PrintOut
End If
End If
Next
Cells(pr.Row, 3).Interior.ColorIndex = 35
End If
WbOpen = ""
bk.Close SaveChanges:=False