Handling multiple errors

[eggtimer]

Hi

I have a workbook with multiple sheets, the sheets contain personal information and the workbook is locked. It is a master sheet and there are many (,000's) of copies saved. when a manager wants to open one of these I have placed the below code in a locked hidden macro sheet that opens when they open excel, and the macro runs from a button on their toolbar. (I've copied the hide code below)

my problem being as people come and go and their sheets are renamed/deleted the macro does not work, i had this covered with the on error statement and all was well, i have now discovered that the 'on error' only appears to work once, so now the macro fails as it is not reaching May18

Can anybody guide me in the best way to overcome this, i have read some posts and tried some solutions for similar problems, such as using 'resume' or -'1' and I can't seem to make them work, i also now wonder if there is a better solution than error handling where i could simply write, if that sheet does not exist move on

Thanks

Wayne

Sub Hidesheets()
On Error GoTo Jan19
ActiveWorkbook.Unprotect Password:="EatYourGreens"
    Sheets("MarkP").Visible = False
    Sheets("PeterD").Visible = False
    Sheets("ClaireF").Visible = False
    Sheets("KarenS").Visible = False
    Sheets("SarahT").Visible = False
    Sheets("RichardF").Visible = False
    Sheets("TomR").Visible = False
    Sheets("JohnP").Visible = False
    Sheets("CharlotteG").Visible = False
    Sheets("AmandaB").Visible = False
    Sheets("HarryR").Visible = False
    Sheets("JamesP").Visible = False
    Sheets("KeithM").Visible = False
    Sheets("SteveC").Visible = False
    Sheets("PeterJ").Visible = False
    Sheets("IanD").Visible = False
    Sheets("KevinH").Visible = False
    Sheets("JaneR").Visible = False
    ActiveWorkbook.Protect Password:="EatYourGreens"
    Exit Sub
Jan19:
On Error GoTo May18
ActiveWorkbook.Unprotect Password:="EatYourGreens"
    Sheets("CarolineT").Visible = False
    Sheets("PeterD").Visible = False
    Sheets("AlexT").Visible = False
    Sheets("KarenS").Visible = False
    Sheets("SarahT").Visible = False
    Sheets("RichardF").Visible = False
    Sheets("TomR").Visible = False
    Sheets("JohnP").Visible = False
    Sheets("CharlotteG").Visible = False
    Sheets("AmandaB").Visible = False
    Sheets("SarahR").Visible = False
    Sheets("SteveC").Visible = False
    Sheets("PeterJ").Visible = False
    Sheets("LeeP").Visible = False
    Sheets("KevinH").Visible = False
    Sheets("JaneR").Visible = False
    ActiveWorkbook.Protect Password:="EatYourGreens"
    Exit Sub
May18:
ActiveWorkbook.Unprotect Password:="EatYourGreens"
    Sheets("CarolineT").Visible = False
    Sheets("PeterD").Visible = False
    Sheets("AlexT").Visible = False
    Sheets("KarenS").Visible = False
    Sheets("SarahT").Visible = False
    Sheets("RichardF").Visible = False
    Sheets("TomR").Visible = False
    Sheets("JohnP").Visible = False
    Sheets("CharlotteG").Visible = False
    Sheets("DavidT").Visible = False
    Sheets("AmandaB").Visible = False
    Sheets("SarahR").Visible = False
    Sheets("SteveC").Visible = False
    Sheets("PeterJ").Visible = False
    Sheets("MargaretR").Visible = False
    Sheets("LeeP").Visible = False
    Sheets("KevinH").Visible = False
    Sheets("JaneR").Visible = False
    ActiveWorkbook.Protect Password:="EatYourGreens"
    End Sub

 

[jasonb75]

You need to kill one error trap before creating another by using On Error Goto 0
Although I'm not entirely sure how reliable that is. I've encountered problems with it previously, but that might have been an error on my part.

Alternatively, assuming that you need to hide all sheets (except for maybe a few specific ones that will never be deleted or renamed) then why not loop through and hide everything except those with something like

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    Select Case ws.Name
        Case "Sheet1", "Sheet3", "Another Sheet"
            ' Do Nothing
        Case Else
            ws.Visible = False
    End Select
Next

Where the first 'Case' line is a list of sheets not to hide.

 

[eggtimer]

Thanks Jason

You are correct I have some sheets that are never hidden\unhidden and then this reverse method works whatever. I thought there was probably a better way. i have been called away now, but i hope to look at this later today, once i have i will get back to you and I assume the unhide is the same procedure just changing visible to true

Thanks again

 

[jasonb75]

I assume the unhide is the same procedure just changing visible to true

It is, but you can simplify the unhide part a little, unless you need some sheets to remain permanently hidden then the name test is not needed. Trying to unhide a sheet that is not hidden will not cause an error.

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
        ws.Visible = True
Next

Or you can have one procedure that will hide and unhide, this will reverse the logic each time it runs.

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    Select Case ws.Name
        Case "Sheet1", "Sheet3", "Another Sheet"
            ' Do Nothing
        Case Else
            ws.Visible = (ws.Visible = False)
    End Select
Next

The line, ws.Visible = (ws.Visible = False) reverses the current .Visible status of the sheets.

I should point out that I have only entered the code into the forum, I haven't checked it in the vba editor for errors.