Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home



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!


Check out our Excel VBA Resources

Re: Run macro when SPECIFIC cell is changed

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


Re: Run macro when SPECIFIC cell is changed

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


Re: Run macro when SPECIFIC cell is changed

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



Re: Run macro when SPECIFIC cell is changed

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.




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.