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
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
VBA Code:
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