Run Macro From An "if" Formula In A Cell

Re: Run Macro From An "if" Formula In A Cell

Hmmm... dont think so, but you could write it so that it calls a User Defined function... that function would then displays a UserForm. The function would return nothing or something depending on your taste....

something like

=If(H14>J14,SHOW_PANIC_SCREEN_FUNCTION(),0)

Ger

Re: Run Macro From An "if" Formula In A Cell

Hi There,

You could do it indirectly by having a macro that runs every time there is a change on your sheet that then calls the userform if H14>J14

For example

Private Sub Worksheet_Change(ByVal Target As Range)


With ActiveSheet
If .Range("H14") > .Range("J14") Then frmPanic.show
End With


End Sub

Does that help?

Re: Run Macro From An "if" Formula In A Cell

There is a way of doing this but not directly from an if formula..

in Visual Basis editor double click on your worksheet then in the main window on the left hand mousedown select Worksheet. then on the Right hand mousedown select Change. Now you can insert code to test the cell that has the IF formula

Private Sub Worksheet_Change(ByVal Target As Range)


    If Cells(1, 1).Value = 1 Then
    
        UserForm1.Show
    else
        'Do Nothing


    End If
    


End Sub

Make your if formula return a value to test IE

=IF(H14>J14,1,"")

Hope this helps

zimitry: D

Re: Run Macro From An "if" Formula In A Cell

Hmmm strange...

Copy and paste the below code into the worksheet Object

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Trim(Cells(1, 2)) = Empty Or Trim(Cells(1, 3)) = Empty Then
   
    Exit Sub
   
    ElseIf Trim(Cells(1, 2).Value) > Trim(Cells(1, 3)) = True Then 'Optional '>=
            Load frmPanic
            frmPanic.Show
        Else
            'Do Nothing
        End If
       


    End If
    


End Sub

This works fine on mine, please see attachment for details..

zimitry

Re: Run Macro From An "if" Formula In A Cell

Changes to a formula's result do not execute the Worksheet Change event. So, this code will execute for any other cell that is changed. This could get cumbersome real quick.

A UDF is a user defined function. You will always see the word Function as part of code for UDF's. They will always return a result.
I would recommend a UDF such as one of the two shown in the attachment.

Of course you can use one of the methods shown earlier for the Worksheet's change event. I would recommend though that Intersect() be used. So, if one were to modify a cell in columns B or C, each of those cells would be checked. One then uses Offset() to get the value of the formula in A. When using this method, no formula is needed in A. Just check the column(s). If B is changed then compare it to C using Offset() and take what action you need.

UDF's are going to be more limited than Sub's in what they can do. Use whichever method that meets your need.

Re: Run Macro From An "if" Formula In A Cell

The way I usually manage that is to create a blank "trigger" sheet. Simple formula reference to the IF function (ie. =Sheet1!A1 is fine) and I use the calculate event of the trigger sheet to run a macro if the result on the trigger sheet equals some value.

Downside is that you have to add a blank sheet to the workbook... but it's easy and it works!

Re: Run Macro From An "if" Formula In A Cell

See attached for a quick example...

The function (UDF) I use is something simple... called "Test" its called in the IF statement in Cell C2. Activate it by changing A2 to "3"

The code behind the UDF "Test" is basic -

Public Function test() As String


UserForm1.Show
test = "Panic Here"


End Function

Since Functions are used to return a value to the Cell, in this example I just use the word "Panic Here", but it could easily be set to 0 or "".

This code is placed in a module. Press ALT+F11 to see it.

Ger

Re: Run Macro From An "if" Formula In A Cell

Cool - thanks for posting back the results. Happy UDF'ing

Ger

Re: Run Macro From An "if" Formula In A Cell

LOL - wow... I love the panic screen... Its hard to miss it : D

Ger

Re: Run Macro From An "if" Formula In A Cell

If you care to allow the user to exit the userform by pressing the ESC key, then add this to your userform object.

Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  If KeyCode = vbKeyEscape Then Unload Me
End Sub

I would guess that you added a conditional format for the budget cells too.

Glad it worked out for you and reporting the details.