Run functiion if cells values change (1 Viewer)

[IainG]

Can anyone tell me if its possible to run a function on sheet1 if any cell values change, while i am working on sheet2.

 

[HaHoBe]

Hi, IainG,

have a look at the Worksheet_Calculate event. Please mind that there are some restrictions to the use of a function when used in a worksheet.

Ciao,
Holger

 

[IainG]

Hi Holger,

I got the Worksheet_Calculate but am struggling to get the correct code to do what I need.

The following code works using the Worksheet_Activate event but i want it to work using Worksheet_calculate! The code needs to refer to say "sheet1" and I cannot seem to get it right!

Any ideas?

If Range("O54") = 0 Then Range("O54").EntireRow.Hidden = True
If Range("O54") > 0 Then Range("O54").EntireRow.Hidden = False

Cheers
Iain.

 

[boblarson]

How about a simple one liner:
Worksheets("Sheet1").Range("O54").EntireRow.Hidden = (Worksheets("Sheet1").Range("O54") = 0)

 

[IainG]

Hi Boblarson,

I tried your code but unfortunately got the following error and crashed Excel!

Method 'Hidden' of Object 'Range' Failed

I need the code to Hide or Unhide the relevant row based on cell value 0 or >0

I have managed to get the function working with the Worksheet_Activate Event but thats not ideal!

Thanks
Iain

 

[namliam]

his code is sound, just make sure your sheet is called "Sheet1", though a wrong sheet name would probably generate "out of range" error.

I would say, check your references to make sure your not missing any...