I'd use a macro that is assigned to the checkbox.
You can assign the same macro to each of the checkboxes from the Forms toolbar.
If the checkbox is checked, then the worksheet is visible:
Option Explicit
Sub testme()
Dim myCBX As CheckBox
Dim wksName As String
Dim wkbkPwd As String
wkbkPwd = "hi"
Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)
wksName = ""
Select Case LCase(myCBX.Name)
Case Is = LCase("check box 1"): wksName = "Sheet2"
Case Is = LCase("check box 2"): wksName = "SheetNameHere"
End Select
If wksName = "" Then
MsgBox "Design error--no sheet assigned to this checkbox"
Exit Sub
End If
'unprotect the workbook
With ThisWorkbook
.Unprotect Password:=wkbkPwd
If myCBX.Value = xlOn Then
.Worksheets(wksName).Visible = xlSheetVisible
Else
.Worksheets(wksName).Visible = xlSheetHidden
End If
.Protect Password:=wkbkPwd, structure:=True, Windows:=False
End With
End Sub
I hadn't thought about protecting the structure. Good Idea. The workbook
that I'm creating is for keeping volleyball stats for High School games and
Club Volleyball Tournaments there will be a couple of dozen teams at the
volleyball club that will be using it. I'm just trying to plan for all of
the possibilities that inexperienced users might encounter. If I can't
figure out code I make it so users can't hide or unhide the sheets manually.
I've used macro recorder to get the following code. I just haven't figured
out how to get the Value = xlOn/xlOff to update the check box yet. I'm still
trying. Thanks.
Sub Checked()
' Checked Macro
'
ActiveSheet.Shapes("Check Box 140").Select
With Selection
.Value = xlOn
End With
ActiveCell.Select
End Sub
Sub UnChecked()
' UnChecked Macro
'
ActiveSheet.Shapes("Check Box 140").Select
With Selection
.Value = xlOff
.LinkedCell = ""
.Display3DShading = False
End With
ActiveCell.Select
End Sub