Check box to show whether a range is visible

UncleBajubjubs

Board Regular
Joined
Jul 11, 2017
Messages
103
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.
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,849
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
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,872
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,020
Messages
5,575,610
Members
412,679
Latest member
TSpan
Top