Worksheet_Calculate or Worksheet_Change?

[Aria Weston]

Hello,
Should I use Worksheet_Calculate or Worksheet_Change in order to trigger
the code once the target cell is updated through formula?

If it's Worksheet_Calculate, how do you rewrite this (code doesn't
work)?

Private Sub Worksheet_Calculate()
Range("A4") 'is the only target cell
If date("A4") > date("A5") and date("A4") < date("A6") then
worksheets("Sheet1").columns("C:E").entirecolumn.hidden = True
End If
End Sub

Do I need application.enableevents=False to start, then end it with
application.enableevents=True?

Thank-you so much,
Aria

 

[Guest]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
x = Worksheets("Sheet1").Cells(4, 1).Value
y = Worksheets("Sheet1").Cells(5, 1).Value
z = Worksheets("Sheet1").Cells(6, 1).Value
If x < y And x < z Then
Columns("C:E").Select
Selection.EntireColumn.Hidden = True
End If
End Sub
Thee macro above will do what you want. Where you put it depends on which
event you want to trap.

 

[Bob Phillips]

It is Calculate, and I would stop events

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
If Range("A4").Value > Range("A5").Value And _
Range("A4").Value < Range("A6").Value Then
Worksheets("Sheet1").Columns("C:E").Hidden = True
End If
Application.EnableEvents = True
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Aria Weston]

Hi Bob and Mike,
How come the code doesn't trigger when cell A4 is updated? I've tried
each code separately, pasted it in Sheet1 (I did not add it to a
module). And Sheet1 does exist in my workbook.

Your help is greatly appreciated.

Thanks,
Aria

 

[Bob Phillips]

It does work. It depends upon a formula in A4, which gets changed by the
source cell(s) being changed, and hides columns in Sheet1. If all these are
true, it works fine.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Aria Weston]

Hi Bob,
Yes, your code works nicely. At first, it wouldn't trigger until I
temporarily deleted the Private out of the signature line. Then
manually activated it. After that, I put the Private back into the
signature line and it's been smooth sailing since. Life's mysteries, I
guess.

Thanks so much,
Aria

 

[Bob Phillips]

How weird! But at least it works now.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)