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

[Bob Adam]

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!!

 

[KennyGreens]

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.

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

 

[dmt32]

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:

 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

 

[bellman101]

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

[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:

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

 

[Bob Adam]

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:

 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

 

[Bob Adam]

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.

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

 

[Bob Adam]

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:

[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:

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