Worksheet_Calculate or Worksheet_Change?

A

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

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

Mike

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.
 
B

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

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

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

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

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

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

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top