VBA Cell Value to hide rows- works on one sheet but not another?

[aidanw]

Hi all,

I'll be the first to admit I'm quite new to VBA, but delving into it as much as I can.
I've been building a questionnaire & want to be able to hide certain rows based on a certain value of a cell.

I have managed this successfully on one sheet within my workbook, however when I copy and paste the code across to another sheet module, in exactly the same format and requesting the exact same function it does not work.

This is the code I have used once already on the sheet which worked;

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E4").Value = False Then
Rows("5:6").EntireRow.Hidden = True
ElseIf Range("E4").Value = True Then
Rows("5:6").EntireRow.Hidden = False
End If
End Sub

I think it makes a difference that I'm using a 'Check box (form control)' from the developer insert menu as an easier way to toggle the value of the cell in E4.
When replicating it in the next sheet, I'm using the same code but just changing the cell to lookup the value & the rows to hide- but it does not want to work.

As a test in the new sheet, I changed the value look up cell to a cell which wasn't linked to the checkbox macro (eg, changing E4 to F4) with the rows that I'm wanting to hide staying the same. I then physically typed in FALSE and TRUE into this cell and it hid the rows that I requested.


My question are...
why does it all of a sudden not like to work on my second sheet as it did on my first?
why does it work if I change the cell lookup to a different cell and physically type in False / True, but it doesn't toggle when I use the macro?

Thanks in Advance!

 

[aidanw]

Additionally- I've realised that it will work by toggling the checkbox macro, however only if, having toggling the checkbox that is linked to the cell stated in the VBA, I then go and toggle another random checkbox macro which is not linked to the VBA at all.
How strange? Why would it need two checkboxes to be toggled before it "recognises" the value change in the cell to be identified in the VBA code?

Tearing my hair out here & I've so nearly finished my project!! argggghhh!!!

 

[Alex Blakenburg]

Clicking the checkbox is not triggering the worksheet change event. So its not until you do something else that Excel is considering a change to that sheet that the macro gets run.
You need to assign a macro to each of your check boxes. It can be the same macro since the macro runs against the activesheet.

The macro would look something like this and if you are going to use it on multiple sheets based on the checkbnox on that sheet you should insert a Module and put it there.

Sub CheckBox1_Click()
    If Range("E4").Value = False Then
        Rows("5:6").EntireRow.Hidden = True
    ElseIf Range("E4").Value = True Then
        Rows("5:6").EntireRow.Hidden = False
    End If
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
Another option

Sub aidanw()
   Rows("5:6").Hidden = Not Range("E4").Value
End Sub

Put this in a standard module & assign it to all the checkboxes.