Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 16

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

  1. #1
    Join Date
    1st July 2006
    Location
    Canberra, Australia
    Posts
    157

    Run Macro From An "if" Formula In A Cell

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

    VB:
    =If(H14>J14,"[COLOR="Green"]frmPanic.show vbmodeless[/COLOR]",0) 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,555

    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
    _______________________________________________
    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

    _______________________________________________

  3. #3
    Join Date
    4th November 2005
    Location
    Hertfordshire, UK
    Posts
    224

    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

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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    15th March 2007
    Location
    LONDON, UK
    Posts
    1,377

    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

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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    1st July 2006
    Location
    Canberra, Australia
    Posts
    157

    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    1st July 2006
    Location
    Canberra, Australia
    Posts
    157

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    15th March 2007
    Location
    LONDON, UK
    Posts
    1,377

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

    Hmmm strange...

    Copy and paste the below code into the worksheet Object

    VB:
    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by Craig Ottley; March 15th, 2007 at 23:31.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    2nd November 2005
    Location
    Tecumseh, OK
    Posts
    1,096

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    10th February 2003
    Location
    Palm Beach, Florida
    Posts
    1,590

    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)

  10. #10
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,555

    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 -

    VB:
    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    _______________________________________________
    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

    _______________________________________________

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 2
    Last Post: September 16th, 2005, 22:32
  2. Replies: 2
    Last Post: August 27th, 2005, 19:19
  3. I'm attempting a complex "IF" & "DAYS360" formula
    By Jon Hook in forum EXCEL HELP
    Replies: 3
    Last Post: March 31st, 2005, 00:39
  4. "Pleading paper": Template? Macro? Formula?
    By geebee in forum EXCEL HELP
    Replies: 2
    Last Post: March 28th, 2005, 13:38
  5. Formating Cell to "Currency" / "Accounting"
    By RPT in forum EXCEL HELP
    Replies: 1
    Last Post: October 12th, 2004, 14:07

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno