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.
