Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Macro activating as result of formula

This is a discussion on Macro activating as result of formula within the Excel Questions forums, part of the Question Forums category; I'm not sure about the stop part of it, however I can confirm that it has entered "Test" into cell ...

  1. #11
    New Member
    Join Date
    Jul 2017
    Posts
    9

    Default Re: Macro activating as result of formula

    I'm not sure about the stop part of it, however I can confirm that it has entered "Test" into cell A1, so it appears to be working from that point of view. It will not change the value of cell A1 unless D15 is negative which is looking good I'm guessing?

  2. #12
    Board Regular
    Join Date
    Oct 2015
    Location
    Mc Calla, AL
    Posts
    170

    Default Re: Macro activating as result of formula

    That's correct. The code I just posted removes the part that was putting Test in A1 and should now call your Mail_Activate code module.

  3. #13
    New Member
    Join Date
    Jul 2017
    Posts
    9

    Default Re: Macro activating as result of formula

    It still had the test bit in, I deleted that and took the Call Mail_ActiveSheet out of quotes and now it appears to be working like a dream. I will do a bit more testing but I'm pretty sure thats it all sorted.

    This concept could have a lot of uses with a little tweaking so this has been unbelievably useful. It has also helped my understanding a bit more!

  4. #14
    Board Regular
    Join Date
    Oct 2015
    Location
    Mc Calla, AL
    Posts
    170

    Default Re: Macro activating as result of formula

    Sorry, I still had the Call Mail_Activate commented out!

    Code:
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")
    
    
    '   If cell in Col AL is changed on Worksheet "Report 3 Import - For PSOs" and Value of D15 changed to <0
    If Target.Column = 38 Then
        If ws.Range("D15").Value < 0 Then
            Range("A1").Value = "Test"
            Call Mail_ActiveSheet
        End If
    End If
    
    
    End Sub

  5. #15
    New Member
    Join Date
    Jul 2017
    Posts
    9

    Default Re: Macro activating as result of formula

    Can you believe i'm back again

    Everything has been going great and I was just about to update the live spreadsheet... slight snag, I probably should've mentioned this before. "Report 3 Import - For PSOs" This sheet is where we copy and paste the data from a report generated from a database, the info in column AL isn't manually entered. I copy from A to AQ and then refresh a pivot table, so I manually entered in a figure that I knew would create the condition to activate the macro but when I copied in and refreshed the table nothing happened.

    Is this just turning into a nightmare now?

  6. #16
    Board Regular
    Join Date
    Oct 2015
    Location
    Mc Calla, AL
    Posts
    170

    Default Re: Macro activating as result of formula

    Okay, Let's try this, place this code on your Worksheet like the previous code. This code fires when the Pivot Table is Refreshed. See if that gets it done for you.
    You are pushing me to the absolute brink of my knowledge here!

    Code:
    Private Sub Worksheet_Calculate()
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")
    
    
    '   If Pivot Table is Updated
        If ws.Range("D15").Value < 0 Then
            Call Mail_ActiveSheet
        End If
    End Sub

  7. #17
    New Member
    Join Date
    Jul 2017
    Posts
    9

    Default Re: Macro activating as result of formula

    haha well at least we're both learning! That seems to work fine except it is generating the e-mail twice...

    Something I seen on another forum which may be of use was this bit of code, dunno if this might be of use?

    PrivateSub Worksheet_Change(ByVal Target As Range)
    IfNot Intersect(Target, Range("A1:C"& ThisWorkbook.Worksheets(1).UsedRange.Rows.Count))IsNothingThen
    'Call your Macro to do stuff
    EndIf
    EndSub

Page 2 of 2 FirstFirst 12

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com