In Excel is there any way to get a cell to activate a Macro?

Question

For example if the value of a cell say A1 <0 then to run a macro that I have created

Hi devilishblueyes, had a quick try tonight and the macro worked but kept running and I could not seem to get the "End if" to work properly but I will keep on trying, Thanks

Answer 1

Yes, there is. You'll need to open the Microsoft Visual Basic Editor (Alt + F11), then double-click the worksheet in the Project Window. At the top of the code window are two drop down combo boxes. Change the left combo box to Worksheet and the right one to Change. Then use an if statement and put your macro code inside the If/End If. You can use the Static statement to grab the old value of A1 before the cell was updated. Below is some example code that will Display a message box when the value of cell A1 is changed. It will only display the message when the value of cell A1 is changed. It uses the Static statement to show the old value of cell A1 before the change and the new value of cell A1. Open a new workbook, open the Visual Basic Editor, double click Sheet1 in the Project window and paste the following code in. Then try the macro out. Change other cells and the message box won't display. Change the value of cell A1 and it will display the old and new value.

Note: [A1] is the same as Range("A1").Value. Using brackets is shortcut notation to state that it is a range. And since Value is the default property for a range you don't have to add the .Value to the end of it.

Private Sub Worksheet_Activate()
KeepValue ([A1])
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyOldValue
MyOldValue = KeepValue([A1])
If MyOldValue <> [A1] Then
MsgBox "Old Value: " & MyOldValue _
& vbCrLf & "New Value: " & [A1], vbOKOnly
End If

End Sub

Function KeepValue(MyValue)
Static OldValue
KeepValue = OldValue
OldValue = MyValue
End Function

PS - Like one other person said. Be careful about doing this too much, especially if the macro is a long macro. You could end up slowing your Excel application down a lot if you aren't careful. You want the if statement as close to the beginning as you can get it. That way it ends the macro as soon as it notices cell A1 hasn't been changed. You don't want to put the If statement at the end so Excel has to run through the entire macro and then find out at the end that "Oh yeah...I didn't need to run all of this."

Answer 2

Macros cannot be assigned directly to a cell. The only way would be to run a macro automatically at start up or opening of the spreadsheet.

Sorry to say but the simple answer is no as it would slow Excel to a crawl to implement that feature.
Every time ANY cell changed it would force Excel to recheck to see if the it needs to run the macro.
Basically You are trying to work in two different modes at the same timeInteractivelt and programmatically (Macro). They are mutually exclusive

Answer 3

What you could do is conditional formatting so if the cell displays <0 it is coloured and have a comment displayed saying that if a cell changes to a certain colour press this button - the button being the macro which you assign to the button.