Macro to Hide Sheet Using Checkbox

JamesA11

New Member
Joined
Oct 2, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Hi guys,

I've put together some macros to hide rows and columns using an ActiveX Checkbox. They work fine but I'm trying to add to them so they also hide a worksheet. An example macro is below.

Cell B10 of the sheet containing the macro has a name that corresponds to the name of another worksheet. I want the checkbox to hide that sheet but I'm struggling to get the reference right.

Can anyone point me in the right direction?

Thanks

VBA Code:
Private Sub CheckBox1_Click()

Application.ScreenUpdating = False

If CheckBox1 = False Then
[10:10].EntireRow.Hidden = True
Else:
[10:10].EntireRow.Hidden = False
End If

If CheckBox1 = False Then
Sheets("Matrix").[7:7].EntireRow.Hidden = True
Else:
Sheets("Matrix").[7:7].EntireRow.Hidden = False
End If

Application.ScreenUpdating = False

End Sub
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,555
Office Version
  1. 2013
Platform
  1. Windows
Not sure where you want it put but here is how you do it:

VBA Code:
Private Sub CheckBox1_Click()
'Modified  10/21/2020  4:48:53 AM  EDT
Application.ScreenUpdating = False
Dim ans As String
ans = Range("B10").Value

If CheckBox1 = False Then
[10:10].EntireRow.Hidden = True
Sheets(ans).Visible = False
Else:
[10:10].EntireRow.Hidden = False
End If

If CheckBox1 = False Then
Sheets("Matrix").[7:7].EntireRow.Hidden = True

Else:
Sheets("Matrix").[7:7].EntireRow.Hidden = False
End If


Application.ScreenUpdating = False

End Sub
 
Solution

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
Slightly different way...

VBA Code:
Private Sub CheckBox1_Click()

Application.ScreenUpdating = False

If CheckBox1 = False Then
[10:10].EntireRow.Hidden = True
Else:
[10:10].EntireRow.Hidden = False
End If

If CheckBox1 = False Then
Sheets(ActiveSheet.Range("B10").Value).Visible = False

Sheets("Matrix").[7:7].EntireRow.Hidden = True
Else:
Sheets(ActiveSheet.Range("B10").Value).Visible = True
Sheets("Matrix").[7:7].EntireRow.Hidden = False

End If



Application.ScreenUpdating = False

End Sub
 

JamesA11

New Member
Joined
Oct 2, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Thanks guys. It's also great to see different ways of doing it.
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
Pleasure. Glad to help.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,409
Messages
5,636,114
Members
416,899
Latest member
thealphaoverseer

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