Hide/Unhide sheets with a checkbox on a mac

[Sharper]

I'm looking for a way to use checkboxes to hide/unhide sheets. I've found code for PCs, but they all use ActiveX. I'm on a Mac and don't have access to ActiveX. Is there a way to accomplish this on a Mac?

TIA

 

[Logit]

..
Untested because I don't have a MAC :

Option Explicit


Sub CheckSheeet()
    If ThisWorkbook.Worksheets(1).Shapes("Check Box 1").OLEFormat.Object.Value = 1 Then
       Sheets("Sheet3").Visible = False
    Else
       Sheets("Sheet3").Visible = True
    End If
End Sub

Paste in a Routine Module. Assign CheckBox1 to the above macro.

 

[Sharper]

..
Untested because I don't have a MAC :

Option Explicit


Sub CheckSheeet()
    If ThisWorkbook.Worksheets(1).Shapes("Check Box 1").OLEFormat.Object.Value = 1 Then
       Sheets("Sheet3").Visible = False
    Else
       Sheets("Sheet3").Visible = True
    End If
End Sub

Paste in a Routine Module. Assign CheckBox1 to the above macro.

Thanks so much. That worked!

 

[Sharper]

..
Untested because I don't have a MAC :

Option Explicit


Sub CheckSheeet()
    If ThisWorkbook.Worksheets(1).Shapes("Check Box 1").OLEFormat.Object.Value = 1 Then
       Sheets("Sheet3").Visible = False
    Else
       Sheets("Sheet3").Visible = True
    End If
End Sub

Paste in a Routine Module. Assign CheckBox1 to the above macro.

Now that this works for one check box. Is there a way to hand 50 or so sheets?

TIA

 

[Logit]

.

This is one way ...

Well ... if you are wanting one checkbox to hide/unhide several sheets at once you can use this macro :

Sub ToggleHideUnhideDataSheets()


Application.ScreenUpdating = False
Dim wb As Workbook
Dim ShtNames() As Variant
Dim i As Integer


Set wb = ActiveWorkbook
ShtNames = Array("Sheet3", "Sheet5", "Sheet9")  '<<--- edit sheet names here. You can add or delete sheet names as well but have at least two for the array.


    For i = 0 To UBound(ShtNames)
        wb.Sheets(ShtNames(i)).Visible = Not wb.Sheets(ShtNames(i)).Visible
    Next i
    
Application.ScreenUpdating = True


End Sub

Then, if you had a second checkbox to control several other sheets, you could duplicate the above macro and edit the sheet names to required.

 

[Sharper]

.

This is one way ...

Well ... if you are wanting one checkbox to hide/unhide several sheets at once you can use this macro :

Sub ToggleHideUnhideDataSheets()


Application.ScreenUpdating = False
Dim wb As Workbook
Dim ShtNames() As Variant
Dim i As Integer


Set wb = ActiveWorkbook
ShtNames = Array("Sheet3", "Sheet5", "Sheet9")  '<<--- edit sheet names here. You can add or delete sheet names as well but have at least two for the array.


    For i = 0 To UBound(ShtNames)
        wb.Sheets(ShtNames(i)).Visible = Not wb.Sheets(ShtNames(i)).Visible
    Next i
    
Application.ScreenUpdating = True


End Sub

Then, if you had a second checkbox to control several other sheets, you could duplicate the above macro and edit the sheet names to required.

I actually need to have a one to one relationship. I plan on having 50 checkboxes and 50 sheets.

 

[Logit]

.
Ok .. then try this :

Option Explicit


Sub CheckSht()


    If ThisWorkbook.Worksheets(1).Shapes("Check Box 1").OLEFormat.Object.Value = 1 Then
       Sheets("Sheet3").Visible = False
    Else
       Sheets("Sheet3").Visible = True
    End If
End Sub

For CheckBox1 name the sub Sub CheckSht1()
For Checkbox2 name the sub Sub CheckSht2()
etc.
etc.

Edit this portion to the sheet corresponding to the Checkbox :

Sheets("Sheet5").Visible = False
 Else
 Sheets("Sheet5").Visible = True

Then for the next sheet :

Sheets("Sheet6").Visible = False
 Else
 Sheets("Sheet6").Visible = True

 

[Sharper]

.
Ok .. then try this :

Option Explicit


Sub CheckSht()


    If ThisWorkbook.Worksheets(1).Shapes("Check Box 1").OLEFormat.Object.Value = 1 Then
       Sheets("Sheet3").Visible = False
    Else
       Sheets("Sheet3").Visible = True
    End If
End Sub

For CheckBox1 name the sub Sub CheckSht1()
For Checkbox2 name the sub Sub CheckSht2()
etc.
etc.

Edit this portion to the sheet corresponding to the Checkbox :

Sheets("Sheet5").Visible = False
 Else
 Sheets("Sheet5").Visible = True

Then for the next sheet :

Sheets("Sheet6").Visible = False
 Else
 Sheets("Sheet6").Visible = True

Perfect. Works. Thanks so much.

 

[Logit]

You are welcome

 

[Sean1993]

Hi, I've used this code and it works for me, however, it jumps to the sheet that I've just unhidden.

Is there anything I cam do to prevent this and stay on the sheet with the option button?

Thanks in advance,
Sean

.

This is one way ...

Well ... if you are wanting one checkbox to hide/unhide several sheets at once you can use this macro :

Sub ToggleHideUnhideDataSheets()


Application.ScreenUpdating = False
Dim wb As Workbook
Dim ShtNames() As Variant
Dim i As Integer


Set wb = ActiveWorkbook
ShtNames = Array("Sheet3", "Sheet5", "Sheet9")  '<<--- edit sheet names here. You can add or delete sheet names as well but have at least two for the array.


    For i = 0 To UBound(ShtNames)
        wb.Sheets(ShtNames(i)).Visible = Not wb.Sheets(ShtNames(i)).Visible
    Next i
    
Application.ScreenUpdating = True


End Sub

Then, if you had a second checkbox to control several other sheets, you could duplicate the above macro and edit the sheet names to required.