[Solved] VBA : Formula result forces a macro action

Hi GrahamB

i think it is possible, haven't tried it out yet but i found a link by Derk which may answer your question:
http://www.ozgrid.com/forum/viewthread.php?tid=2551

cheers
xlite

hi GrahamB

i followed Derk's suggestion and tried it out with a code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("a1") > 100 Then
myMacro
End If
End Sub

Sub myMacro()
MsgBox "The end cost is higher than 100", vbInformation
End Sub

works really well

cheers
xlite

Quote

Originally posted by xlite
hi GrahamB

i followed Derk's suggestion and tried it out with a code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("a1") > 100 Then
myMacro
End If
End Sub

Sub myMacro()
MsgBox "The end cost is higher than 100", vbInformation
End Sub

works really well

cheers
xlite

Display More

I have one similar that I use in a sheet that requires data entry from users.. and i have it not only bring a message box, but undo the action that forced the cell A1 to go over the 100.. here's my example.. just goes in the sheet code..

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("a1") > 100 Then
MsgBox "The end cost is higher than 100"
Application.Undo
End If
End Sub

Hi Graham,

OK, two points:

1. We are dealing here with what is known as worksheet event code. This does not reside in a general module but in the sheet object. Right-click on the sheet tab concerned and select 'View Code' - this will take you to where the event code is kept (and needs to be in order to work).

2. If you want the macro triggered by the result of a formula then the event that you want is that Calculate event.

So, in the event code for the sheet:

Private Sub Worksheet_Calculate()
    If Range("A1").Value > 100 Then Call MyMacro
    'call macro if A1 > 100
End Sub

And in a general module:

Sub MyMacro()
    MsgBox "Hello"
End Sub

Now, if the formula in A1 is something like "=B1+C1" if the combined values of those cells are greater than 100 when the Calculate event is triggered then your macro will be called.

HTH