Trouble with Error Handler

[hellfire45]

I need some help managing my error handlers on this subroutine.
Basically, it is opening up a workbook and checking to make sure that the target worksheets exist and the proper number of columns are in those worksheets and if not, it goes down to the error handlers.

When it gets to the Worksheets(“Tasks”).select line, if this iworksheet isn't there then its supposed to go to error handler SonGoku, which basically just kills exits the sub. But, when it gets down to the worksheets(“Messages”).select line, if this worksheet isn’t there, I want to give the person the option to continue without the worksheet. I want it to go to Error Handler Gohan, which upon selecting “yes” will skip to checkX which will bypass the “messages” section.

The problem is that when the program gets down to the worksheets(“Messages”).select line, it throws an error because the worksheet is missing but instead of going to Gohan, its going to Goku and terminating, thus not giving the user the option to continue. Does anybody know why this is occurring?

Thank you so much!

Sub subx()

filepath4 = "F:\document\d\Client Systems and Trending\Master Workflow RAW Data\C\"
docname4 = "CLF_Workload-" & Format(Date, "yyyy-mm-dd") & ".xls"

Workbooks.Open filepath4 & docname4, ReadOnly:=True


On Error GoTo SonGoku
Worksheets("Tasks").Select


If WorksheetFunction.counta(Sheets("Tasks").Rows("1:1")) <> 6 Then
MsgBox ("Data Inconsistencies in Clarifire Tasks Sheet")
Workbooks(docname4).Close False
Exit Sub


On Error GoTo Gohan
Worksheets("Messages").Select


On Error GoTo Gohan
ElseIf WorksheetFunction.counta(Sheets("Messages").Rows("1:1")) <> 7 Then
On Error GoTo Gohan
 MsgBox ("Data Inconsistencies in C Messages Sheet")
 Workbooks(docname4).Close False
Exit Sub
Else
End If

Checkx:


Call datacompiler



Exit Sub


'----------------------------------------------------ERROR HANDLERS------------------------------------------------------------------
SonGoku:
Application.DisplayAlerts = False
 MsgBox ("Worksheet(aka tab) Missing or wrong name")
 ActiveWorkbook.Close
  Exit Sub
  
  
  
  
Gohan:
   MSGX = MsgBox("No C Messages Found.  Continue WITHOUT C Messages??", vbYesNo, "C MESSAGES NOT FOUND")
         If MSG2X = vbYes Then
                            MsgBox ("Continuing...")
                         GoTo Checkx
                         
                        Else 'start else 2'
                        MsgBox ("Teminating Program")
                        Exit Sub
    End If


End Sub

 

[mrmmickle1]

I tried stepping through some of your code that I modified and it seemed to work OK with the way I have it but I found an error you may want to clear up:

I added a worksheet to my workbook named "Tasks" and stepped through this code and it seems to work fine.

[COLOR=#0000ff]Sub[/COLOR] subx()

[COLOR=#0000ff]On Error GoTo[/COLOR] SonGoku
Worksheets("Tasks").Select

[COLOR=#0000ff]On Error GoTo[/COLOR] Gohan
Worksheets("Messages").Select

[COLOR=#008000]'----------------------------------------------------ERROR HANDLERS------------------------------------------------------------------[/COLOR]
SonGoku:
Application.DisplayAlerts = [COLOR=#0000ff]False[/COLOR]
 MsgBox ("Worksheet(aka tab) Missing or wrong name")
 ActiveWorkbook.Close
[COLOR=#0000ff]  Exit Sub[/COLOR]
  
Gohan:
   MSGX = MsgBox("No C Messages Found.  Continue WITHOUT C Messages??", vbYesNo, "C MESSAGES NOT FOUND")
         [COLOR=#0000ff]If [/COLOR][B][COLOR=#ff0000]MSG2X [/COLOR][/B]= vbYes T[COLOR=#0000ff]hen[/COLOR]
         
            MsgBox ("Continuing...")
                         
        [COLOR=#0000ff] Else[/COLOR] [COLOR=#008000]'start else 2'[/COLOR]
             MsgBox ("Teminating Program")
[COLOR=#0000ff]         End If[/COLOR]
[COLOR=#0000ff]             Exit Sub[/COLOR]
[COLOR=#0000ff]   End Sub  [/COLOR]

MSGX and MSG2X are not consistent. Also you do not need parentheses around your MsgBox text. You can just write:

    MsgBox "Continuing..."

Did you try stepping through your code using F8 to see which line of code does not perform as expected??