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

aidanw

New Member
Joined
Feb 23, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

aidanw

New Member
Joined
Feb 23, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Feb 23, 2021
Messages
403
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,517
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Another option
VBA Code:
Sub aidanw()
   Rows("5:6").Hidden = Not Range("E4").Value
End Sub
Put this in a standard module & assign it to all the checkboxes.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,505
Messages
5,625,199
Members
416,080
Latest member
blemon

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