Macro toggle button

[cdharguth]

Hi all,

I've assigned two macros to two separate shapes. One macro protects sheets in the workbook:


Sub ProtectSheets()
Dim wsheet As Worksheet
For Each wsheet In ActiveWorkbook.Worksheets
wsheet.Protect Password:="econdev"
Next wsheet
End Sub

The other unprotects:


Sub UnprotectSheets()
Dim wsheet As Worksheet
For Each wsheet In ActiveWorkbook.Worksheets
wsheet.Unprotect Password:="econdev"
Next wsheet
End Sub

Rather than have two separate buttons I would like to merge them into one, toggling between the text "Hide Sheets" and "Unhide Sheets." When the button is first clicked, the UnhideSheets macro will run and the button text will switch to "Hide Sheets" and run the HideSheets macro with the next click.

Please advise!

[Paul_Hossler]

This toggles the protection status for each sheet

Note it is possible to get out of sync if you manually (or with VBA) add sheets while the rest are protected



Option Explicit


Sub ToggleProtect()
Dim wsheet As Worksheet
For Each wsheet In ActiveWorkbook.Worksheets
With wsheet
If .ProtectContents Then
.Unprotect Password:="econdev"
Else
.Protect Password:="econdev"
End If
End With
Next wsheet
End Sub

[cdharguth]

Paul-

This is excellent. Your code worked like a charm. Is it possible to have the shape text toggle between "Lock Sheets" and "Unlock Sheets" as I run each macro? I've been playing with the following code:


Sub ToggleProtect()

Dim wb As Workbook
Dim ws As Worksheet
Dim wsheet As Worksheet

For Each wsheet In ActiveWorkbook.Worksheets
With wsheet
If .ProtectContents Then
.Unprotect Password:="econdev"
Else
.Protect Password:="econdev"
End If
End With
Next wsheet

If ws.Shapes("btn_ToggleProtect").TextFrame.Characters.Text = "Lock Sheets" Then
ws.Shapes("btn_ToggleProtect").TextFrame.Characters.Text = "Unlock Sheets"
Else
ws.Shapes("btn_ToggleProtect").TextFrame.Characters.Text = "Lock Sheets"
End If
End Sub

[Paul_Hossler]

Edit -- little more cleanup





Option Explicit


Sub ToggleProtect()
Dim wsheet As Worksheet, wsOriginal As Worksheet

Application.ScreenUpdating = False

Set wsOriginal = ActiveSheet

For Each wsheet In ActiveWorkbook.Worksheets
With wsheet
If .ProtectContents Then
.Unprotect Password:="econdev"
Else
.Protect Password:="econdev"
End If
End With
Next


'this is the sheet with the button
With Worksheets("Sheet1")
If .ProtectContents Then
.Unprotect Password:="econdev"
.Shapes("btn_ToggleProtect").TextFrame.Characters.Text = "Unlock Sheets"
.Protect Password:="econdev"
Else
.Shapes("btn_ToggleProtect").TextFrame.Characters.Text = "Lock Sheets"
End If
End With


wsOriginal.Select


Application.ScreenUpdating = True


End Sub

[cdharguth]

This is exactly what I needed. Bravo and thank you!