Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Handling | Excel Programming | |||
Error handling with a handling routine | Excel Programming | |||
Error handling | Excel Programming | |||
Error Handling | Excel Programming | |||
Error Handling | Excel Programming |