none
VBA code to create Alert when IF statment is met RRS feed

  • Question

  • Please help! I am trying to create a VBA code to display an alert box (vbExclamation) when the IF statement in cells J2:J24 is TRUE.  I've been trying with:

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Range("J2:J24")=true Then

    MsgBox "Alert! One or more alerts have been activated!", vbExclamation, "ALERT!"

    End If End Sub

    Help!

    Thank you!!!

    Sunday, March 20, 2016 2:54 AM

Answers

  • From the MsgBox text in your example I am assuming that you mean if any cell in the range is True then display the alert and not if all cells in the range are True. If so, then the following code in the Calculate event in lieu of change event.

    Private Sub Worksheet_Calculate()
       
        Dim rngToSearch As Range
        Dim rngToFind As Range
       
        Set rngToSearch = Range("J2:J24")
       
        With rngToSearch
            Set rngToFind = .Find(What:="true", _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False, _
                        SearchFormat:=False)
        End With
       
        If Not rngToFind Is Nothing Then    'Not Nothing then is something so found
            MsgBox "Alert! One or more alerts have been activated!", vbExclamation, "ALERT!"
        End If

    End Sub


    Regards, OssieMac

    • Marked as answer by Kayla33 Thursday, March 24, 2016 8:49 PM
    Sunday, March 20, 2016 10:55 AM

All replies

  • From the MsgBox text in your example I am assuming that you mean if any cell in the range is True then display the alert and not if all cells in the range are True. If so, then the following code in the Calculate event in lieu of change event.

    Private Sub Worksheet_Calculate()
       
        Dim rngToSearch As Range
        Dim rngToFind As Range
       
        Set rngToSearch = Range("J2:J24")
       
        With rngToSearch
            Set rngToFind = .Find(What:="true", _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False, _
                        SearchFormat:=False)
        End With
       
        If Not rngToFind Is Nothing Then    'Not Nothing then is something so found
            MsgBox "Alert! One or more alerts have been activated!", vbExclamation, "ALERT!"
        End If

    End Sub


    Regards, OssieMac

    • Marked as answer by Kayla33 Thursday, March 24, 2016 8:49 PM
    Sunday, March 20, 2016 10:55 AM
  • Hi OssieMac, thanks so much for your help.  I'm still unable to get the code to work. You are correct, that we would want the messagebox to alert whenever any of the values in the range are true. If you have email I could forward you the sheet? It's very basic, however the VBA code is not basic!!!

    Thanks again.

    Kayla

    Sunday, March 20, 2016 3:47 PM
  • Hi,

    Try: Debug.Print Evaluate("COUNTIF(A1:A5,TRUE)")

    The result is a count of the number of cells that are TRUE. You can put the formula =COUNTIF(A1:A5,TRUE) in a cell and test that as well if preferred.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Monday, March 21, 2016 4:38 AM
  • Hi Rod, tried that, and the result was 0; however, there were 2 cells that were true in the range.. I've double checked my formulas and all appear to be fine.  I'm confused?? Here's my formula in J2:J24 =IF(OR(B2<H2, B2=H2, B2>G2, B2=G2),"ALERT","-")

    Tuesday, March 22, 2016 2:48 AM
  • My formula works if your formulas return TRUE or FALSE which is what you suggested was happening in your original post. The formula above returns "ALERT" if the OR returns TRUE.

    So make your formula =COUNTIF(A1:A5,"ALERT")


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Thursday, March 24, 2016 2:03 AM
  • Gotcha. My apologies, I was thinking true as in "logic if true", I should have been more clear. Changed "True" to "Alert" in the vba code Ozzie provided, and it worked! Thanks again for your help, much appreciated!
    Thursday, March 24, 2016 8:44 PM