One text box for two macros, with variable text

dot102223

New Member
Joined
Jan 31, 2012
Messages
5
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?
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
wouldnt you be better off with a commandbutton?
just change .caption and the function can be switched in the macro
 

dot102223

New Member
Joined
Jan 31, 2012
Messages
5
I got it using this:

Sub protect()
Dim sPassword As String

On Error Resume Next
If Sheets("Updates").Shapes.Range(Array("TextBox 6")).TextFrame.Characters.text = "Protect/Hide" Then
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
Sheets("Updates").Shapes.Range(Array("TextBox 6")).TextFrame.Characters.text = "Unprotect"
Else
sPassword = InputBox("Enter Your Password")
If sPassword = "DT2008" Then
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
Sheets("Updates").Shapes.Range(Array("TextBox 6")).TextFrame.Characters.text = "Protect/Hide"
Else
MsgBox "Wrong password!"
Exit Sub
End If


End If

End Sub
 

Forum statistics

Threads
1,144,699
Messages
5,725,824
Members
422,644
Latest member
BethGS

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top