Excel VBA - Using checkboxes to hide/unhide worksheets

Shelby21

New Member
Joined
Nov 21, 2017
Messages
29
Hi guys,

I need some help with a spreadhseet I have been working on for months.
I'm not very good with VBA and don't think there is any other way to accomplish what I am trying to do below.

Any help would be much appreciated!

This spreadsheet has the following tabs:

DC_Info
Instructions
LP
IR_Setup
PASS
CR_Setup
IR_Teaching
CR_Teaching
Robot_Teaching_Verifications
Wafer_Slip_DCP's
KMTerm_Commands
SU3200_Shims
Revision_History

What I am trying to do:

The table below is located in the Instructions tab.

I would like to link each checkbox to its corresponding worksheet

Checking the box makes the worksheet visible

Unchecking the box hides the worksheet

I would also like to be able to center the checkboxes within column E and have solid border for the checkbox instead of just the two line bordered checkboxes shown below

If possible, a last item would be to highlight cell of the worksheet name yellow if the checkbox is checked.

setup.PNG
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
457
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi Shelby21,

Welcome to MrExcel board.

1. I have used ActiveX controls on excel sheet.

2. Used below code (event handler) for each checkbox in Instruction Sheets
a. To visible/ Hide the worksheet
b. to change color of cell to yellow

VBA Code:
Private Sub DC_Info_Click()
    If DC_Info.Value = True Then
        Sheets("DC_Info").Visible = True
        Sheets("Instructions").Range("D4").Interior.Color = vbYellow
    Else
        Sheets("DC_Info").Visible = False
        Sheets("Instructions").Range("D4").Interior.Color = vbWhite
    End If
End Sub

Private Sub LP_Click()
     If LP.Value = True Then
        Sheets("LP").Visible = True
        Sheets("Instructions").Range("D5").Interior.Color = vbYellow
    Else
        Sheets("LP").Visible = False
        Sheets("Instructions").Range("D5").Interior.Color = vbWhite
    End If
End Sub

3. Change the Left Property to 195 for all checkboxes. (see screenshot)
4. Change the Special Effect Property to 0-frmButtonEffectFlat. (see screenshot)
5. Use below code in Workbook Open event to hide all worksheets.

VBA Code:
Private Sub Workbook_Open()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "Instructions" Then
            ws.Visible = False
        End If
    Next
End Sub
 

Attachments

  • checkboxProperties.JPG
    checkboxProperties.JPG
    51.3 KB · Views: 5
  • AfterSelecting.JPG
    AfterSelecting.JPG
    66 KB · Views: 8
  • Initial-WBOpen.JPG
    Initial-WBOpen.JPG
    61.8 KB · Views: 8
Solution

Shelby21

New Member
Joined
Nov 21, 2017
Messages
29
Hi Saura,

Thank you so much for your quick reply and detailed instructions. I did change all my checkboxes to activeX controls as well as changing the checkbox properties as you suggested.

I had one more question regarding the vba code you provided.

I changed the name of checkbox1 to DC_Info and then copied your code into the window as shown in the attached image, but I am unable to click on the checkbox to select it.

I must be missing some simple step here to activate the checkbox controls to allow me to check and uncheck the box.
 

Attachments

  • excel.PNG
    excel.PNG
    59.4 KB · Views: 4

Shelby21

New Member
Joined
Nov 21, 2017
Messages
29
Hi Saura,

Thank you so much for your quick reply and detailed instructions. I did change all my checkboxes to activeX controls as well as changing the checkbox properties as you suggested.

I had one more question regarding the vba code you provided.

I changed the name of checkbox1 to DC_Info and then copied your code into the window as shown in the attached image, but I am unable to click on the checkbox to select it.

I must be missing some simple step here to activate the checkbox controls to allow me to check and uncheck the b
 

Shelby21

New Member
Joined
Nov 21, 2017
Messages
29

ADVERTISEMENT

disregard my last post. I got it to work by selecting run in developer. I'll continue to enter all the code and let you know if I run into any other issues. Thanks again!
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,676
Office Version
  1. 2013
Platform
  1. Windows
You said:
and don't think there is any other way to accomplish what I am trying to do below.

A lot easier way would be to double click on "Alpha" to Hide sheet named "Alpha"
And the cell with "Alpha" would turn "Yellow"
Double click on "Alpha" again and the sheet named "Alpha" would be unhidden and "Cell" With "Alpha" would turn "Green"

My script runs when you double click on any cell in column 2
Modify this to the proper column with sheet names.


This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
Only this one script is needed for all cells in column 2

VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  5/30/2021  1:44:23 AM  EDT
Cancel = True
On Error GoTo M
If Target.Column = 2 Then
If Target.Value = "" Then Exit Sub

Select Case Sheets(Target.Value).Visible
    Case False
        Sheets(Target.Value).Visible = True
        Target.Interior.Color = vbGreen
    Case True
         Sheets(Target.Value).Visible = False
        Target.Interior.Color = vbYellow
End Select
End If
Exit Sub

M:
MsgBox "The sheet named " & Target.Value & " Does not exist"
Target.Interior.Color = vbRed

End Sub
 

Shelby21

New Member
Joined
Nov 21, 2017
Messages
29

ADVERTISEMENT

Hi My Answer is This,

Thank you for your response. I agree that I do think your one script would be easier to write, however, I do prefer the checkbox method. I'm developing this spreadsheet for many co-workers to use during troubleshooting robot issues with our equipment so I think having a checkbox next to each worksheet will be easier for them to understand and follow.

I just finished writing and testing Saura's method and it is what I was looking to do with this spreadsheet.

I do appreciate your help though :)
 

Shelby21

New Member
Joined
Nov 21, 2017
Messages
29
I have one follow up to this request.

When I enabled the password protect the macro for the checkboxes is working, but cell B4 is no longer being highlighted yellow when I select the checkbox.

Does anyone know why this would be happening after I protect the workbook?

prot.PNG



When I click on Debug, the following code is displayed in my vba editor:

debug.PNG
 

Shelby21

New Member
Joined
Nov 21, 2017
Messages
29
I think I know what is causing this issue.

I need to select the checkbox for format cells on this tab after I select protect for the macro to work properly.

I also use a macro button to protect all my worksheets which is somehow not enabling the format cells.

Seems like I need to do some more troubleshooting on my end to determine if my protect/unprotect macro is causing the issue.
 

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
457
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi, if you have protected the workbook then before selecting Checkbox you need to unprotect the workbook. See below.

1234 is the password to unprotect/ protect the sheet.
VBA Code:
Private Sub DC_Info_Click()
'****to unprotect the workbook
ThisWorkbook.Unprotect ("1234")
    If DC_Info.Value = True Then
        Sheets("DC_Info").Visible = True
        Sheets("Instructions").Range("D4").Interior.Color = vbYellow
    Else
        Sheets("DC_Info").Visible = False
        Sheets("Instructions").Range("D4").Interior.Color = vbWhite
    End If
'****to protect the workbook
ThisWorkbook.Protect ("1234")
End Sub
 

Forum statistics

Threads
1,141,298
Messages
5,705,565
Members
421,399
Latest member
hjweiss00

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
Top