Worksheet_Calculate or Worksheet_Change?
 

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.h idden = 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

*** Sent via Developersdex http://www.developersdex.com ***

Worksheet_Calculate or Worksheet_Change?
 

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.


"Aria Weston" wrote:

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.h idden = 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

*** Sent via Developersdex http://www.developersdex.com ***

Worksheet_Calculate or Worksheet_Change?
 

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" wrote in message
...
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.h idden = 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

*** Sent via Developersdex http://www.developersdex.com ***

Worksheet_Calculate or Worksheet_Change?
 

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

*** Sent via Developersdex http://www.developersdex.com ***

Worksheet_Calculate or Worksheet_Change?
 

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" wrote in message
...
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 :)

*** Sent via Developersdex http://www.developersdex.com ***

Worksheet_Calculate or Worksheet_Change?
 

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

*** Sent via Developersdex http://www.developersdex.com ***

Worksheet_Calculate or Worksheet_Change?
 

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)



"Aria Weston" wrote in message
...
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 :)

*** Sent via Developersdex http://www.developersdex.com ***