Hide and unhide sheets by checking a checkbox

[TheIntern]

Hello everyone,

I am currently designing a workbook, which will have around 40 sheets. Is it possible to create a sheet with a check box after the name of each sheet and the sheets are (un)hidden, if you check the box?

Thanks in advance,

The Intern

 

[jasonb75]

Something like

Sheets("Sheet1").Visible = (ActiveSheet.Shapes("Box1").ControlFormat.Value = xlOn)

 

[TheIntern]

Thanks, that sounds good.
Would you mind explaining it a bit further for me?
Do I just need to paste this in VBA into the sheet with the boxes, or assign it to the specified check box?

 

[Sektor]

Say, you have 3 sheets with following names: Report1, Report2, Report3. Then to automatically hide/unhide these sheets, you should name three checkboxes as:
Checkbox1_Report1, Checkbox2_Report2, Checkbox3_Report3. Actually, the name before "_" delimiter does not matter. Then you the code will look like this:

Private Sub CheckBox1_Report1_Click()
    Sheets(Right$(CheckBox1_Report1.Name, Len(CheckBox1_Report1.Name) - InStr(CheckBox1_Report1.Name, "_"))).Visible = CheckBox1_Report1.Value
End Sub

As you have 50 checkboxes, it's more flexible to create one event handler for all checkboxes, but I ain't sure you can understand the logic behind. If you're ready, then I'm gonna explain mechanics.

 

[jasonb75]

Both, sorry, I should have explained that.

As an afterthought, maybe a better way to reduce the amount of code.

Use form control checkboxes, not activex.

Change the box name so that it is exactly the same as the sheet it's being linked to (in the named range box when you have the checkbox selected).

Then you could assign this single module of code to all of the checkboxes, which gets the sheetname from the box as it is clicked.

Sub show_sheets()
Dim sName As String
sName = Application.Caller
Sheets(sName).Visible = (ActiveSheet.Shapes(sName).ControlFormat.Value = xlOn)
End Sub

edit:- similar to the suggestion Sektor made while I was typing, but less complicated

 

[TheIntern]

I am still doing something wrong...
I pasted the code:

Sub show_sheets()
Dim sName As String
sName = Application.Caller
Sheets(sName).Visible = (ActiveSheet.Shapes(sName).ControlFormat.Value = xlOn)
End Sub

I used a form control checkbox and named it like the sheet. I enabled macros and saved as an macro enabled workbook. But there is still nothing happening, if ich check the box
Any Ideas? I am using Office 2007, btw.

Thanks again for your help!

 

[Sektor]

Try this: Checkboxes.xlsm
Press "Скачать" button.

You can add as many checkboxes as you want. If don't like pattern "Checkbox_[Sheet Name]", then I can correct this.

 

[klarowe]

I have a very similar project and this is how I made up my index. Its a lot of lines of code, but its really not too bad and easy to change when needed.

Private Sub CheckBox1_Click()
    Sheets("1st Stg Impeller").Visible = CheckBox1.Value
    Application.Run "IndexNumber"
End Sub
Private Sub CheckBox2_Click()
    Sheets("2nd Stg Impeller").Visible = CheckBox2.Value
    Application.Run "IndexNumber"
End Sub
Private Sub CheckBox3_Click()
    Sheets("1st_2nd Stg Pinion").Visible = CheckBox3.Value
    Application.Run "IndexNumber"
End Sub
Private Sub CheckBox4_Click()
    Sheets("1st Stg Laby").Visible = CheckBox4.Value
    Application.Run "IndexNumber"
End Sub
Private Sub CheckBox5_Click()
    Sheets("2nd Stg Laby").Visible = CheckBox5.Value
    Application.Run "IndexNumber"
End Sub
Private Sub CheckBox6_Click()
    Sheets("1st Stg Tiebolt_Nut").Visible = CheckBox6.Value
    Application.Run "IndexNumber"
End Sub
Private Sub CheckBox7_Click()
    Dim i As Integer
    For i = 1 To 6
        ActiveSheet.OLEObjects("CheckBox" & i).Object.Value = ActiveSheet.CheckBox7.Value
    Next i
End Sub

The last one is for a Select / Deselect all checkbox. And you would have to change the sheet names to match as well as the checkbox names (activeX). The Application.Run "IndexNumber" is just a numbering system I had that would automatically re-number the index depending on what sheets were hidden.

 

[jasonb75]

I am still doing something wrong...
I pasted the code:

Sub show_sheets()
Dim sName As String
sName = Application.Caller
Sheets(sName).Visible = (ActiveSheet.Shapes(sName).ControlFormat.Value = xlOn)
End Sub

I used a form control checkbox and named it like the sheet. I enabled macros and saved as an macro enabled workbook. But there is still nothing happening, if ich check the box
Any Ideas? I am using Office 2007, btw.

Thanks again for your help!

Did you assign the macro to the checkbox?

Note that the code is check to show, uncheck to hide, if your sheet is already visible then nothing will happen the first time you check the box.

 

[TheIntern]

I don't know, why jasonb75s version is not working in my case...anyway, klarowes macro runs smoothly
Thanks for your help everyone!!!