Check box to show whether a range is visible

UncleBajubjubs

Board Regular
Joined
Jul 11, 2017
Messages
111
Office Version
  1. 2010
Hello,
I am creating a form in which the user can toggle whether sections are hidden using a check box. When the user clicks the check box, it should hide and unhide the section.
I inserted a checkbox and assigned it the following macro:

VBA Code:
Sub Toggle()
If Range("LEHours").EntireRow.Hidden = True Then
Range("LEHours").EntireRow.Hidden = False
Else
Range("LEHours").EntireRow.Hidden = True
End If
End Sub

This works at hiding and unhiding the section, but once clicked, the checkbox stays checked even when you press it again. How might I make the checkbox properly show unchecked when the section is hidden?

Thanks.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
If you are using a checkbox from the forms menu (not ActiveX), you could assign it to code like this
VBA Code:
Sub CheckBox1_Click()
    With ActiveSheet.Shapes(Application.Caller).ControlFormat
        Range("LEHours").EntireRow.Hidden = (.Value = xlOff)
    End With
End Sub
 
Upvote 0
And the Active-X equivalent (also works with Userform checkbox)
VBA Code:
Private Sub CheckBox1_Click()
    Range("LEHours").EntireRow.Hidden = Not Range("LEHours").EntireRow.Hidden
    CheckBox1.Value = Range("LEHours").EntireRow.Hidden
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

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