Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Trigger a macro from a formula result! (Excel 2000 >)

    Hi,
    I was reading <post:=531,343>post 531,343</post:> and based on the replies, i was wondering if we can tweak this further and set it up to allow the results of an IF() function to trigger a macro. Say.... IF(Condition = True, Run a macro, "Not Allowed")

    I know there is the Calculate event, but this runs irrespective of the functions results, and for any function!!

    Any idea's?
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trigger a macro from a formula result! (Excel 2000 >)

    Not the way you state it. You can create a custom worksheet function in VBA, but keep in mind that worksheet functions cannot modify worksheet cells in any way except by returning a function value.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Trigger a macro from a formula result! (Excel 2000 >)

    I understand.

    Can it be done in another way? Something like: Attaching a macro to the calculate event that cancels the event if the function is not an IF() function and if it is an IF() function, and the function result is a certain value, to run code then....

    EG:
    Private Sub Worksheet_Calculate()
    If NOT IF() then
    Cancel = True
    Else
    If Cell that was calculated = 1 then
    Insert code to run here
    Else
    Cell that was calculated . value = "Not Allowed"
    End If
    End If
    End Sub

    Jeez...if programming was as easy as this!!

    Am I thinking in realistic terms??
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trigger a macro from a formula result! (Excel 2000 >)

    The Worksheet_Calculate event doesn't "know" which functions are being evaluated.

    It might be easier if you gave a concrete example of what you want to accomplish.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trigger a macro from a formula result! (Excel 2000 >)

    I would use the Worksheet_Change event for this, and look at C4 because that is the cell that triggers the change:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C4")) Is Nothing Then
    Application.EnableEvents = False
    If Range("C6") > 6500000 Then
    Shell "Notepad.exe", vbNormalFocus
    End If
    Application.EnableEvents = True
    End If
    End Sub

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Trigger a macro from a formula result! (Excel 2000 >)

    <P ID="edit" class=small>(Edited by Rudi on 03-Nov-05 11:24. Hans...It just dawns on me that you could say in the Calculate event.... If Range("C7").value > 6500000 then Open NotePad...
    But this defeats the theory of this thread. If the sheet had multiple tests on it, then this approach would be tedious!

    PS: I hope I am not confusing the matter....sometimes I run on a tangent and don't think things through before I post the request! I appologise in advance if this is becoming a "Black Hole" thread!
    )</P>Is this sample workable?

    (Instructions inside!)
    Regards,
    Rudi

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Trigger a macro from a formula result! (Excel 2000 >)

    OK...After my doubts about the validity of my request...you actually got it working.

    Puurrrfect

    Thanx Hans, your a star!
    Regards,
    Rudi

Posting Permissions

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