Hide Column When Checkbox (Forms Control) is checked

[hrayani]

Hello All,

The checkbox (Forms Control) is linked with cell D2 which returns True when the checkbox is checked and False when its unchecked

Then I am using the below code to hide/unhide rows

Private Sub Worksheet_Change(ByVal Target As Range)
    
If Range("D2").Value = True Then Rows("1:1").EntireRow.Hidden = True
If Range("D2").Value = False Then Rows("1:1").EntireRow.Hidden = False


End Sub

The problem is every time I need to edit any cell in the worksheet for the event to trigger.
Is there any possibility to hide or unhide rows as soon as the checkbox is checked or unchecked.

I also tried the selection change event but for that too I need to click on a cell for the event to trigger.

Any help would be appreciated.

Regards,

Humayun

 

[abdelfattah]

Hello All,

The checkbox (Forms Control) is linked with cell D2 which returns True when the checkbox is checked and False when its unchecked


View attachment 7740

Then I am using the below code to hide/unhide rows

Private Sub Worksheet_Change(ByVal Target As Range)
   
If Range("D2").Value = True Then Rows("1:1").EntireRow.Hidden = True
If Range("D2").Value = False Then Rows("1:1").EntireRow.Hidden = False


End Sub

The problem is every time I need to edit any cell in the worksheet for the event to trigger.
Is there any possibility to hide or unhide rows as soon as the checkbox is checked or unchecked.

I also tried the selection change event but for that too I need to click on a cell for the event to trigger.

Any help would be appreciated.

Regards,

Humayun

try add this line in your code

Application.EnableEvents = True

 

[hrayani]

Hi Abdelfattah,

Thanks for the reply...

I added the line you suggested..... but nothing is happening

I still have to edit a cell for the event to trigger

Any idea ??

 

[Fluff]

How about assigning this macro to the checkbox

Sub hrayani()
   Dim Shp As Shape
   
   Set Shp = ActiveSheet.Shapes(Application.Caller)
   Rows(1).Hidden = Shp.ControlFormat.Value = 1
End Sub

 

[hrayani]

Hi Fluff,

Thanks as always for you help.... your solution is working great

Can you please tell me that is it also possible to call a macro when the checkbox is checked

 

[Fluff]

Just add

Call Macro1

to the code & change macro1 to the name of your macro

 

[abdelfattah]

Hi Abdelfattah,

Thanks for the reply...

I added the line you suggested..... but nothing is happening

I still have to edit a cell for the event to trigger

Any idea ??

you know buddy when i use your code also it doesn't work me too but when i add this line

Application.EnableEvents = True

it really works it is strange doesn't work with you but i 'm asking myself why doesn't work the code from the beginning it usually any code works when put in event change worksheet it is the mystery i hope find the answered from the experts

 

[Fluff]

The change event does not get triggered if a cell value changes as the result of a checkbox being (un)selected.

 

[hrayani]

Just add

Call Macro1

to the code & change macro1 to the name of your macro

Hi Fluff,

I will try this and will get back to you Tomorrow. Not sure in which line to add this “Call Macro 1” though

 

[Fluff]

It doesn't matter where you add it