Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting worksheet_Calculate inside worksheet_CHANGE | Excel Programming | |||
Worksheet_calculate() | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming |