Hide and unhide sheets by checking a checkbox

TheIntern

New Member
Joined
Mar 15, 2011
Messages
30
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Something like

Code:
Sheets("Sheet1").Visible = (ActiveSheet.Shapes("Box1").ControlFormat.Value = xlOn)
 
Upvote 0
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?
 
Upvote 0
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:
Code:
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.
 
Upvote 0
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.

Code:
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 :)
 
Last edited:
Upvote 0
I am still doing something wrong...
I pasted the code:
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 :mad:
Any Ideas? I am using Office 2007, btw.

Thanks again for your help!
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
Code:
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.
 
Upvote 0
I am still doing something wrong...
I pasted the code:
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 :mad:
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.
 
Upvote 0
I don't know, why jasonb75s version is not working in my case...anyway, klarowes macro runs smoothly :biggrin:
Thanks for your help everyone!!!
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

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
Back
Top