Announcement

Collapse
No announcement yet.

Run Macro From An "if" Formula In A Cell

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Run Macro From An "if" Formula In A Cell

    Is it possible to call a Userform from an "IF" statement in a cell?
    eg.

    Code:
    =IF(H14>J14,"frmPanic.show vbmodeless",0)

  • #2
    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

    Check out our new reputation system. Click on the "star" under the post!
    _______________________________________________

    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

    Comment


    • #3
      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

      Code:
      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?

      Comment


      • #4
        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

        Code:
         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

        Comment


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

          Ok sound good.
          This is the second reference to a "UDF" I have encountered tonight.
          How do I write one and where do I put it when I do?
          Sorry I am becoming more of a "NOOB" the more I learn. Lol

          Comment


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

            Firstly thanks for your help on this.

            Ian, I inserted your code in the Worksheet however it is not the active sheet at the time the data is entered and sum of both cells "H14" and "J14" is the result of off sheet calculations. In your code it refers to the 'activesheet' could this be a problem?

            zimitry, likewise I inserted your code in the worksheet,change event and no result, Which cell exactly should the formula reside in I tried A1 ala (1,1).
            Am I off the mark here.

            Both sets of code look the part and make sense but neither have the desired result?

            Comment


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

              Hmmm strange...

              Copy and paste the below code into the worksheet Object

              Code:
              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
              Attached Files
              Last edited by Craig Ottley; March 15th, 2007, 22:31.

              Comment


              • #8
                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.
                Attached Files

                Comment


                • #9
                  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!
                  Sub All_Macros(Optional control As Variant)

                  Comment


                  • #10
                    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 -

                    Code:
                    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
                    Attached Files

                    Check out our new reputation system. Click on the "star" under the post!
                    _______________________________________________

                    There are 10 types of people in the world. Those that understand Binary and those that dont.

                    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

                    The BEST Lookup function of all time

                    Dynamic Named Ranges are your bestest friend

                    _______________________________________________

                    Comment


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

                      Ger Plante,
                      Thanks, I got the gist of your post and after a bit of "web surfing" and a bit of trial an error created my first UDF and have it running successfully.
                      Always something new to learn.
                      Thanks for the direction.

                      Comment


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

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

                        Ger

                        Check out our new reputation system. Click on the "star" under the post!
                        _______________________________________________

                        There are 10 types of people in the world. Those that understand Binary and those that dont.

                        Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

                        The BEST Lookup function of all time

                        Dynamic Named Ranges are your bestest friend

                        _______________________________________________

                        Comment


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

                          An example workbook for others to browse.
                          Attached Files

                          Comment


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

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

                            Ger

                            Check out our new reputation system. Click on the "star" under the post!
                            _______________________________________________

                            There are 10 types of people in the world. Those that understand Binary and those that dont.

                            Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

                            The BEST Lookup function of all time

                            Dynamic Named Ranges are your bestest friend

                            _______________________________________________

                            Comment


                            • #15
                              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.
                              Code:
                              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.

                              Comment

                              Working...
                              X