Excel Macro programming unhide/hide a sheet based on a CALCULATED cell value

Bob Adam

New Member
Joined
Aug 15, 2018
Messages
4
Dear experts. I am new to Excel Macro and I would like to create an Excel Macro programming code so that it will unhide/hide one certain page based on a calculated cell value. More specifically, I would like to find a value "B1-A1" (A1 and B1 are dates), with a conditional statement on "C1" (explained below), if "B1-A1<180 (half a year)" then unhide the "Data page", if not then hide the "Data page".

The code I am currently using is :

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("C1")) Is Nothing Then
If Range("C1").Value = 1 And Not IsEmpty(Range("C1")) Then
Sheets("Data Form").Visible = xlSheetVisible
Else
Sheets("Data Form").Visible = xlSheetHidden
End If
End If
End Sub

Here I am using a "IF statement" in cell C1, if B1-A1=<180 then C1=1 (unhide page), otherwise C1=0 (hide page). The PROBLEM I am having is the code does not run if the value in C1 is calculated ("1" or "0" based on "A1-B1" condition). But if I enter "1" or "0" from keyboard it works.

Please give me some help to solve this problem.

Many thanks in advance!!
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hi, this should work, I feel like the code you posted is over-complicating things. Let me know if this would work, or why you are checking if the cell is empty.

Code:
Private Sub Worksheet_Change()

If Sheets("enter your source sheet here").Range("C1").Value = 1 Then 'enter the source sheet
    Sheets("Data Form").Visible = xlSheetVisible
Else
    Sheets("Data Form").Visible = xlSheetHidden
End If

End Sub
 
Last edited:
Upvote 0
Hi,
welcome to forum
As you have discovered, Worksheet_Change event does not trigger when formula value changes.

To test if a cell containing a formula has been changed, one option would be to use the Calculation event but as this event has no Target argument, its use therefore, involves a bit of trickery like this:

Code:
 Private Sub Worksheet_Calculate()    
With Me.Range("C1")
    If .Value <> .ID Then
        .ID = .Value
     Sheets("Data Form").Visible = IIf(.Value = 1, xlSheetVisible, xlSheetVeryHidden)
    End If
    End With
End Sub

ID property of the range is used as a placeholder to check if value of cell has changed.

Dave
 
Last edited:
Upvote 0
Bob, I believe the code is not firing on calculation, due to this line:

Code:
[COLOR=#333333]If Not Application.Intersect(Target, Range("C1")) Is Nothing Then[/COLOR]

when the value within cell C1 changes, that doesn't mean C1 is the target cell. C1 will only be the target when the formula is edited - so for example when you manually type '1' or '0' in the cell.

Try replacing this line with:

Code:
If Target.row = 1 AND Target.column<4 Then 'This restricts the code to only fire upon changes in Cells A1, B1 or C1

I have explained why your code was not working as expected, BUT - having said that, I would just change the whole lot for Kenny's suggestion
EDIT: Or Dave's suggestion now I've seen it! :)

Cheers
JB
 
Last edited:
Upvote 0
Thank you so much Dave! This worked BEAUTIFULLY!! You truly saved my day!:)


Hi,
welcome to forum
As you have discovered, Worksheet_Change event does not trigger when formula value changes.

To test if a cell containing a formula has been changed, one option would be to use the Calculation event but as this event has no Target argument, its use therefore, involves a bit of trickery like this:

Code:
 Private Sub Worksheet_Calculate()    
With Me.Range("C1")
    If .Value <> .ID Then
        .ID = .Value
     Sheets("Data Form").Visible = IIf(.Value = 1, xlSheetVisible, xlSheetVeryHidden)
    End If
    End With
End Sub

ID property of the range is used as a placeholder to check if value of cell has changed.

Dave
 
Upvote 0
Thank you very much Kenny. I tried your code, replaced "enter your source sheet" with my page, but it seems not working. . But I really appreciate your help!:)

Hi, this should work, I feel like the code you posted is over-complicating things. Let me know if this would work, or why you are checking if the cell is empty.

Code:
Private Sub Worksheet_Change()

If Sheets("enter your source sheet here").Range("C1").Value = 1 Then 'enter the source sheet
    Sheets("Data Form").Visible = xlSheetVisible
Else
    Sheets("Data Form").Visible = xlSheetHidden
End If

End Sub
 
Upvote 0
Thank you very much JB. I tried your code, it works fine with keyboard input but not with calculated value. But I really appreciate your help! :)

Bob, I believe the code is not firing on calculation, due to this line:

Code:
[COLOR=#333333]If Not Application.Intersect(Target, Range("C1")) Is Nothing Then[/COLOR]

when the value within cell C1 changes, that doesn't mean C1 is the target cell. C1 will only be the target when the formula is edited - so for example when you manually type '1' or '0' in the cell.

Try replacing this line with:

Code:
If Target.row = 1 AND Target.column<4 Then 'This restricts the code to only fire upon changes in Cells A1, B1 or C1

I have explained why your code was not working as expected, BUT - having said that, I would just change the whole lot for Kenny's suggestion
EDIT: Or Dave's suggestion now I've seen it! :)

Cheers
JB
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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