MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Run macro when SPECIFIC cell is changed


Posted by John on April 05, 2000 4:49 AM

Apologies is this is an obvious question, but I'm fairly new to Excel macros.

I have some code that I need to run whenever Cell A1 is changed.

I've tried messing about with the Worksheet_Change event. I can get it to work when ANY cell is changed, but I need it to run whenever A1 (and only A1) is changed.

I've searched the acrhive and looked at the tips section, but still can't work out how to do this.

A simple explanation would be appreciated!


Posted by Celia on April 05, 2000 8:02 AM


John
Try :-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Call YourMacro
End If
End Sub

Celia

Posted by nishith on April 07, 2000 6:43 AM

You can try worksheet_calculate event if you are working with numbers,,,

It is fairly simple and you can get your answere from the help documentation of excel...

I suppose this will also run fast then worksheet change event because this event is only called when you do some calculations

still you cant do it with it write me a mail i will provide you code on this message board

nishith

Posted by Celia on April 08, 2000 2:26 AM


Nishith

As far as I know, none of the event procedures make any discernible difference to Excel's performance and there is no discernible difference in speed between one procedure and another - is there?

The Worksheet_Change event occurs when the value of a cell is changed except if the change is the result of a calculation.

The Worksheet_Calculate event occurs only when the worksheet is calculated but not when there is a change other than by calculation.

What is the actual Worksheet_Calculate code you would suggest for Brian to use?

In a reply to Forcing a macro to run - Jamie 19:38:38 3/24/100, Ivan Moala suggested using Workbook_SheetChange. This would occur whenever cell A1 changed (by whatever means) with the following code :-

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldVal
If Range("A1").Value <> OldVal Then
Call yourMacro
OldVal = Range("A1").Value
End If
End Sub

Celia


Posted by Celia on April 08, 2000 2:28 AM


Nishith

As far as I know, none of the event procedures make any discernible difference to Excel's performance and there is no discernible difference in speed between one procedure and another - is there?

The Worksheet_Change event occurs when the value of a cell is changed except if the change is the result of a calculation.

The Worksheet_Calculate event occurs only when the worksheet is calculated but not when there is a change other than by calculation.

What is the actual Worksheet_Calculate code you would suggest for Brian to use?

In a reply to Forcing a macro to run - Jamie 19:38:38 3/24/100, Ivan Moala suggested using Workbook_SheetChange. This would occur whenever cell A1 changed (by whatever means) with the following code :-

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldVal
If Range("A1").Value <> OldVal Then
Call yourMacro
OldVal = Range("A1").Value
End If
End Sub

Celia

PS. Sorry, I meant John - not Brian.