formula help

[ishabir]

Is there an event in Excel such as ON EXIT of a cell or when moving from a cell
it can calculate a formula.

For example, in cell B8 i want to have =(x*y)-z

I want the formula to be in code and hidden because not everyone should be able
to see it or know it.

Any help much appreciated.

Ishabir

 

[SIXTH SENSE]

hi!
place your code on the on change event of excel.

here!

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
<SPAN style="color:#007F00">' your code here</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

This will be executed each time you make an edit in any cell within that worksheet.

 

[ishabir]

thanks for that sixth sense. However, I am having a problem with this line, which keeps on crashing my machine.

Range("B23").Formula = Range("B8").Value / Range("B4").Value

Any ideas?

Thanks,

ishabir

 

[SIXTH SENSE]

Hi!
I works fine in me.
Just make sure that B4 is not zero or else you get an error.

to avoid that paste this before your code.

On error resume next
Range("B23")= Range("B8").Value / Range("B4").Value