Currently I have to separate text boxes linked to two separate macros. Text box 1 is a "Protect/Hide" macro, which sets a number of sheets to be very hidden and protects all sheets in the workbook. The code for this macro:
Sub ProtectSheets()
On Error Resume Next
Worksheets("Stylists").Columns("AF:AS").EntireColumn.Hidden = True
Worksheets("Stylists").protect Password:="DT2008"
Worksheets("massage").Columns("AF:AX").EntireColumn.Hidden = True
Worksheets("massage").protect Password:="DT2008"
Worksheets("nails").Columns("AF:AW").EntireColumn.Hidden = True
Worksheets("nails").protect Password:="DT2008"
Worksheets("estheticians").Columns("AF:AZ").EntireColumn.Hidden = True
Worksheets("estheticians").protect Password:="DT2008"
Worksheets("Makeup").Columns("AM:BG").EntireColumn.Hidden = True
Worksheets("Makeup").protect Password:="DT2008"
Worksheets("Chandler").Visible = xlVeryHidden
Worksheets("Borgata").Visible = xlVeryHidden
Worksheets("Arrowhead").Visible = xlVeryHidden
Worksheets("MasterControl").Visible = xlVeryHidden
Worksheets("Import").Visible = xlVeryHidden
Worksheets("Scales").Visible = xlVeryHidden
End Sub
I also have an "Unprotect" text box which links to the following macro:
Sub UnprotectSheets()
On Error Resume Next
Worksheets("Stylists").Unprotect Password:="DT2008"
Worksheets("Stylists").Columns("AF:AS").EntireColumn.Hidden = False
Worksheets("massage").Unprotect Password:="DT2008"
Worksheets("massage").Columns("AF:AX").EntireColumn.Hidden = False
Worksheets("nails").Unprotect Password:="DT2008"
Worksheets("nails").Columns("AF:AW").EntireColumn.Hidden = False
Worksheets("estheticians").Unprotect Password:="DT2008"
Worksheets("estheticians").Columns("AF:AZ").EntireColumn.Hidden = False
Worksheets("Makeup").Unprotect Password:="DT2008"
Worksheets("Makeup").Columns("AM:BG").EntireColumn.Hidden = False
Worksheets("Chandler").Visible = True
Worksheets("Borgata").Visible = True
Worksheets("Arrowhead").Visible = True
Worksheets("Arrow Mill Data").Visible = True
Worksheets("MasterControl").Visible = True
Worksheets("Import").Visible = True
Worksheets("Scales").Visible = True
End Sub
What I want to do is have just one text box to control both of these macros. The text box should read "Protect/Hide" if the sheets are visible and nothing is protected. When the box is clicked, the text should change to "Unprotect" and the protect macro should be executed. When the box is clicked again, the text should change to "Protect/Hide" and the unprotect macro should be executed. Is this possible?
Sub ProtectSheets()
On Error Resume Next
Worksheets("Stylists").Columns("AF:AS").EntireColumn.Hidden = True
Worksheets("Stylists").protect Password:="DT2008"
Worksheets("massage").Columns("AF:AX").EntireColumn.Hidden = True
Worksheets("massage").protect Password:="DT2008"
Worksheets("nails").Columns("AF:AW").EntireColumn.Hidden = True
Worksheets("nails").protect Password:="DT2008"
Worksheets("estheticians").Columns("AF:AZ").EntireColumn.Hidden = True
Worksheets("estheticians").protect Password:="DT2008"
Worksheets("Makeup").Columns("AM:BG").EntireColumn.Hidden = True
Worksheets("Makeup").protect Password:="DT2008"
Worksheets("Chandler").Visible = xlVeryHidden
Worksheets("Borgata").Visible = xlVeryHidden
Worksheets("Arrowhead").Visible = xlVeryHidden
Worksheets("MasterControl").Visible = xlVeryHidden
Worksheets("Import").Visible = xlVeryHidden
Worksheets("Scales").Visible = xlVeryHidden
End Sub
I also have an "Unprotect" text box which links to the following macro:
Sub UnprotectSheets()
On Error Resume Next
Worksheets("Stylists").Unprotect Password:="DT2008"
Worksheets("Stylists").Columns("AF:AS").EntireColumn.Hidden = False
Worksheets("massage").Unprotect Password:="DT2008"
Worksheets("massage").Columns("AF:AX").EntireColumn.Hidden = False
Worksheets("nails").Unprotect Password:="DT2008"
Worksheets("nails").Columns("AF:AW").EntireColumn.Hidden = False
Worksheets("estheticians").Unprotect Password:="DT2008"
Worksheets("estheticians").Columns("AF:AZ").EntireColumn.Hidden = False
Worksheets("Makeup").Unprotect Password:="DT2008"
Worksheets("Makeup").Columns("AM:BG").EntireColumn.Hidden = False
Worksheets("Chandler").Visible = True
Worksheets("Borgata").Visible = True
Worksheets("Arrowhead").Visible = True
Worksheets("Arrow Mill Data").Visible = True
Worksheets("MasterControl").Visible = True
Worksheets("Import").Visible = True
Worksheets("Scales").Visible = True
End Sub
What I want to do is have just one text box to control both of these macros. The text box should read "Protect/Hide" if the sheets are visible and nothing is protected. When the box is clicked, the text should change to "Unprotect" and the protect macro should be executed. When the box is clicked again, the text should change to "Protect/Hide" and the unprotect macro should be executed. Is this possible?