Excel alerts

V

Valentino

Board Regular
Joined
Mar 28, 2010
Messages
105
Hi everyone,

In an excel file i am refreshing data from a web query every 10 minutes. In the sheet i have some if formulas which check whether certain conditions have been met. If these are met, i would like to get a notification or alert, not necessarily by email but just in excel as i have the file open all day but just not scanning it all the time. Is there an easy way to do this, or can it only be done by vba? Is there some example of this solution/vba script?

Many thanks for your thoughts!!!

Valentino
 
Sort by date Sort by votes
johnnyL said:
See if this eliminates the problems you discussed:

VBA Code: 
Private Sub Worksheet_Calculate()
'
'   V1.2
'   1st 10 minute refresh will create the destination if it doesn't exist & will save the Formula column results to create a base line to compare to.
'   All other 10 minute refreshes will compare the current formula column to the previous formula column and display the row #s that changed to '1' or '-1'.
'
'   Check the lines at the top of the script that end with ' <---
'       Those lines are the lines that may need to be changed to reflect your particular setup.
'
'
    Dim FormulaStartRow                 As Long, LastRowAssetColummn    As Long
    Dim DestinationSheet                As String
    Dim AssetColumn                     As String, FormulaColumn        As String
    Dim wsDestination                   As Worksheet, wsSource          As Worksheet
'
    DestinationSheet = "TenMinuteUpdates"                           ' <--- Set this to the name of the sheet to store 10 minute results into
    Set wsSource = ThisWorkbook.Sheets("Sheet1")                    ' <--- Set this to the sheetname that has the '1's & '0's
'
      FormulaColumn = "E"                                           ' <--- Set this to the formula Column letter
    FormulaStartRow = 2                                             ' <--- Set this to the start row of formulas in the FormulaColumn
       AssetColumn = "A"                                            ' <--- Set this to the Asset Column letter, this column is used to determine last row
'
    LastRowAssetColummn = wsSource.Range(AssetColumn & Rows.Count).End(xlUp).Row ' Determine last row of data
'
    If Application.CountIf(wsSource.Range(FormulaColumn & FormulaStartRow & _
            ":" & FormulaColumn & LastRowAssetColummn), "1") > 0 Or _
            Application.CountIf(wsSource.Range(FormulaColumn & FormulaStartRow & _
            ":" & FormulaColumn & LastRowAssetColummn), "-1") > 0 Then  ' If the range contains any value of 1 or -1 then ...
'
        Dim DestinationSheetExists      As Boolean
        Dim FormulaColumnRow            As Long, OutputArrayRow As Long
        Dim LastDestinationColumnNumber As Long
        Dim RowOffset                   As Long
        Dim AssetColumnArray            As Variant, FormulaColumnArray          As Variant
        Dim OutputArray                 As Variant, PreviousFormulaResultArray  As Variant
'
        On Error Resume Next                                        '   Bypass error generated in next line if sheet does not exist
        Set wsDestination = ThisWorkbook.Sheets(DestinationSheet)   '   Assign DestinationSheet to wsDestination
        On Error GoTo 0                                             '   Turn Excel error handling back on
'
        If Not wsDestination Is Nothing Then DestinationSheetExists = True      '   Check to see if the DestinationSheet exists
'
' Create DestinationSheet if it doesn't exist
        If DestinationSheetExists = False Then                                  '   If DestinationSheet does not exist then ...
            ThisWorkbook.Sheets.Add(After:=wsSource).Name = DestinationSheet    '       Create the DestinationSheet after the Source sheet
            Set wsDestination = ThisWorkbook.Sheets(DestinationSheet)           '       Assign the DestinationSheet to wsDestination
        End If
'
' Save Assets into array
        AssetColumnArray = wsSource.Range(AssetColumn & _
                FormulaStartRow & ":" & AssetColumn & _
                LastRowAssetColummn)                                '   Save the values of the Asset Column range into the 2D 1 based AssetColumnArray RC
'
' Save formulas into array
        FormulaColumnArray = wsSource.Range(FormulaColumn & _
                FormulaStartRow & ":" & FormulaColumn & _
                LastRowAssetColummn)                                '   Save the values of the formula Column range into the 2D 1 based FormulaColumnArray RC
'
        ReDim OutputArray(1 To UBound(FormulaColumnArray))          '   Establish # of rows in 1D 1 based OutputArray
'
' Create Saved formula result column on DestinationSheet
        If wsDestination.Range("A1") = vbNullString Then
            wsDestination.Range("A1") = Date                        '   Display the Date on DestinationSheet
            wsDestination.Range("A2") = Time()                      '   Display the Time on DestinationSheet
            wsDestination.Range("A3") = "------------------"        '   Display spacer line on DestinationSheet
'
            wsDestination.Range("A4").Resize(UBound(FormulaColumnArray)) = _
                FormulaColumnArray                                  '   Display results to DestinationSheet
'
            wsDestination.UsedRange.EntireColumn.AutoFit            '   Autofit all of the columns
'
            GoTo SubExit
        End If
'
' Load previous formula results into array
        PreviousFormulaResultArray = wsDestination.Range("A4:A" & _
                wsDestination.Range("A" & Rows.Count).End(xlUp).Row)    '   Load previous formula results into PreviousFormulaResultArray
'
        OutputArrayRow = 0                                          '   Initialize OutputArrayRow to zero
        RowOffset = FormulaStartRow - LBound(FormulaColumnArray)    '   Determine Row difference between FormulaStartRow and start row of FormulaColumnArray
'
'-------------------------------------------------------------------
'
        For FormulaColumnRow = 1 To UBound(FormulaColumnArray, 1)   '   Loop through the FormulaColumnArray to check for '1's & '-1's
            If FormulaColumnArray(FormulaColumnRow, 1) = "1" Or _
                    FormulaColumnArray(FormulaColumnRow, 1) = "-1" Then '       If a '1' or '-1' is found then ...
                If PreviousFormulaResultArray(FormulaColumnRow, 1) = 0 Then '       If previous value was '0' then ...
                    OutputArrayRow = OutputArrayRow + 1                 '           Increment OutputArrayRow
'
                    OutputArray(OutputArrayRow) = "(" & _
                            FormulaColumnArray(FormulaColumnRow, 1) & _
                            ") " & AssetColumnArray(FormulaColumnRow, 1)    '           Save the changed to value & Asset into OutputArray
                End If
            End If
        Next                                                        '   Loop Back
'
        LastDestinationColumnNumber = wsDestination.Cells.Find("*", _
                , xlFormulas, , xlByColumns, xlPrevious).Column     '   Get last Column Number used in the DestinationSheet
'
        wsDestination.Cells(1, LastDestinationColumnNumber + 1) = Date      '   Display the Date on DestinationSheet
        wsDestination.Cells(2, LastDestinationColumnNumber + 1) = Time()    '   Display the Time on DestinationSheet
        wsDestination.Cells(3, LastDestinationColumnNumber + 1) = "------------------"  '   Display spacer line on DestinationSheet
'
        wsDestination.Cells(4, LastDestinationColumnNumber _
                + 1).Resize(UBound(OutputArray)) = _
                Application.Transpose(OutputArray)                  '   Display results to DestinationSheet
'
'Save current formula results to the DestinationSheet
        wsDestination.Range("A1") = Date                            '   Display the Date on DestinationSheet
        wsDestination.Range("A2") = Time()                          '   Display the Time on DestinationSheet
        wsDestination.Range("A3") = "------------------"            '   Display spacer line on DestinationSheet
'
        wsDestination.Range("A4").Resize(UBound(FormulaColumnArray)) = _
            FormulaColumnArray                                      '   Display results to DestinationSheet
'
        wsDestination.UsedRange.EntireColumn.AutoFit                '   Autofit all of the columns
    End If
'
'-------------------------------------------------------------------
'
SubExit:
End Sub
Click to expand...
This seems to
johnnyL said:
See if this eliminates the problems you discussed:

VBA Code: 
Private Sub Worksheet_Calculate()
'
'   V1.2
'   1st 10 minute refresh will create the destination if it doesn't exist & will save the Formula column results to create a base line to compare to.
'   All other 10 minute refreshes will compare the current formula column to the previous formula column and display the row #s that changed to '1' or '-1'.
'
'   Check the lines at the top of the script that end with ' <---
'       Those lines are the lines that may need to be changed to reflect your particular setup.
'
'
    Dim FormulaStartRow                 As Long, LastRowAssetColummn    As Long
    Dim DestinationSheet                As String
    Dim AssetColumn                     As String, FormulaColumn        As String
    Dim wsDestination                   As Worksheet, wsSource          As Worksheet
'
    DestinationSheet = "TenMinuteUpdates"                           ' <--- Set this to the name of the sheet to store 10 minute results into
    Set wsSource = ThisWorkbook.Sheets("Sheet1")                    ' <--- Set this to the sheetname that has the '1's & '0's
'
      FormulaColumn = "E"                                           ' <--- Set this to the formula Column letter
    FormulaStartRow = 2                                             ' <--- Set this to the start row of formulas in the FormulaColumn
       AssetColumn = "A"                                            ' <--- Set this to the Asset Column letter, this column is used to determine last row
'
    LastRowAssetColummn = wsSource.Range(AssetColumn & Rows.Count).End(xlUp).Row ' Determine last row of data
'
    If Application.CountIf(wsSource.Range(FormulaColumn & FormulaStartRow & _
            ":" & FormulaColumn & LastRowAssetColummn), "1") > 0 Or _
            Application.CountIf(wsSource.Range(FormulaColumn & FormulaStartRow & _
            ":" & FormulaColumn & LastRowAssetColummn), "-1") > 0 Then  ' If the range contains any value of 1 or -1 then ...
'
        Dim DestinationSheetExists      As Boolean
        Dim FormulaColumnRow            As Long, OutputArrayRow As Long
        Dim LastDestinationColumnNumber As Long
        Dim RowOffset                   As Long
        Dim AssetColumnArray            As Variant, FormulaColumnArray          As Variant
        Dim OutputArray                 As Variant, PreviousFormulaResultArray  As Variant
'
        On Error Resume Next                                        '   Bypass error generated in next line if sheet does not exist
        Set wsDestination = ThisWorkbook.Sheets(DestinationSheet)   '   Assign DestinationSheet to wsDestination
        On Error GoTo 0                                             '   Turn Excel error handling back on
'
        If Not wsDestination Is Nothing Then DestinationSheetExists = True      '   Check to see if the DestinationSheet exists
'
' Create DestinationSheet if it doesn't exist
        If DestinationSheetExists = False Then                                  '   If DestinationSheet does not exist then ...
            ThisWorkbook.Sheets.Add(After:=wsSource).Name = DestinationSheet    '       Create the DestinationSheet after the Source sheet
            Set wsDestination = ThisWorkbook.Sheets(DestinationSheet)           '       Assign the DestinationSheet to wsDestination
        End If
'
' Save Assets into array
        AssetColumnArray = wsSource.Range(AssetColumn & _
                FormulaStartRow & ":" & AssetColumn & _
                LastRowAssetColummn)                                '   Save the values of the Asset Column range into the 2D 1 based AssetColumnArray RC
'
' Save formulas into array
        FormulaColumnArray = wsSource.Range(FormulaColumn & _
                FormulaStartRow & ":" & FormulaColumn & _
                LastRowAssetColummn)                                '   Save the values of the formula Column range into the 2D 1 based FormulaColumnArray RC
'
        ReDim OutputArray(1 To UBound(FormulaColumnArray))          '   Establish # of rows in 1D 1 based OutputArray
'
' Create Saved formula result column on DestinationSheet
        If wsDestination.Range("A1") = vbNullString Then
            wsDestination.Range("A1") = Date                        '   Display the Date on DestinationSheet
            wsDestination.Range("A2") = Time()                      '   Display the Time on DestinationSheet
            wsDestination.Range("A3") = "------------------"        '   Display spacer line on DestinationSheet
'
            wsDestination.Range("A4").Resize(UBound(FormulaColumnArray)) = _
                FormulaColumnArray                                  '   Display results to DestinationSheet
'
            wsDestination.UsedRange.EntireColumn.AutoFit            '   Autofit all of the columns
'
            GoTo SubExit
        End If
'
' Load previous formula results into array
        PreviousFormulaResultArray = wsDestination.Range("A4:A" & _
                wsDestination.Range("A" & Rows.Count).End(xlUp).Row)    '   Load previous formula results into PreviousFormulaResultArray
'
        OutputArrayRow = 0                                          '   Initialize OutputArrayRow to zero
        RowOffset = FormulaStartRow - LBound(FormulaColumnArray)    '   Determine Row difference between FormulaStartRow and start row of FormulaColumnArray
'
'-------------------------------------------------------------------
'
        For FormulaColumnRow = 1 To UBound(FormulaColumnArray, 1)   '   Loop through the FormulaColumnArray to check for '1's & '-1's
            If FormulaColumnArray(FormulaColumnRow, 1) = "1" Or _
                    FormulaColumnArray(FormulaColumnRow, 1) = "-1" Then '       If a '1' or '-1' is found then ...
                If PreviousFormulaResultArray(FormulaColumnRow, 1) = 0 Then '       If previous value was '0' then ...
                    OutputArrayRow = OutputArrayRow + 1                 '           Increment OutputArrayRow
'
                    OutputArray(OutputArrayRow) = "(" & _
                            FormulaColumnArray(FormulaColumnRow, 1) & _
                            ") " & AssetColumnArray(FormulaColumnRow, 1)    '           Save the changed to value & Asset into OutputArray
                End If
            End If
        Next                                                        '   Loop Back
'
        LastDestinationColumnNumber = wsDestination.Cells.Find("*", _
                , xlFormulas, , xlByColumns, xlPrevious).Column     '   Get last Column Number used in the DestinationSheet
'
        wsDestination.Cells(1, LastDestinationColumnNumber + 1) = Date      '   Display the Date on DestinationSheet
        wsDestination.Cells(2, LastDestinationColumnNumber + 1) = Time()    '   Display the Time on DestinationSheet
        wsDestination.Cells(3, LastDestinationColumnNumber + 1) = "------------------"  '   Display spacer line on DestinationSheet
'
        wsDestination.Cells(4, LastDestinationColumnNumber _
                + 1).Resize(UBound(OutputArray)) = _
                Application.Transpose(OutputArray)                  '   Display results to DestinationSheet
'
'Save current formula results to the DestinationSheet
        wsDestination.Range("A1") = Date                            '   Display the Date on DestinationSheet
        wsDestination.Range("A2") = Time()                          '   Display the Time on DestinationSheet
        wsDestination.Range("A3") = "------------------"            '   Display spacer line on DestinationSheet
'
        wsDestination.Range("A4").Resize(UBound(FormulaColumnArray)) = _
            FormulaColumnArray                                      '   Display results to DestinationSheet
'
        wsDestination.UsedRange.EntireColumn.AutoFit                '   Autofit all of the columns
    End If
'
'-------------------------------------------------------------------
'
SubExit:
End Sub
Click to expand...
This seems to work! :-) no errors anymore !!!

Do you think the 3 condition idea from post 26 is difficult to achieve...?

Thanks so much for all your help :-) xD
 
Upvote 0
Is the column E still what the '-1' or '1' change is being based on?
 
Upvote 0
Johnny,

no, the "-1" or "1" change can now be in columns B-D, so it tests 3 possible conditions (see picture attached).
- If for Asset1 eg column B changed into "1", the output should be "Asset1: Plus-Condition1"
- If for Asset 1 eg column B and C changed into "1", the output should be "Asset1: Plus-Condition1, Plus-Condition1"
- If for Asset1 eg column B changed into "-1", the output should be "Asset1: Minus-Condition1"

--> Would it be possible to make this condition1/2/3 dynamic, eg name dependent on cells b1/c1/d1?

Sounds not too complex i hope....try to make it clear....
THANKS!
 

Attachments

  • Pic4.JPG
    Pic4.JPG
    26.8 KB · Views: 8
Upvote 0
You are basically asking another question that is far from what this thread originally started asking. I suggest you create a new thread because this thread, in its current state, has nothing to do with what you are asking now.
 
Upvote 0
johnnyL said:
You are basically asking another question that is far from what this thread originally started asking. I suggest you create a new thread because this thread, in its current state, has nothing to do with what you are asking now.
Click to expand...
Fully agree, the solution is different from original intention, but thats how problems are solved, finding alternative solutions and being flexible. Also this is still about giving alerts (although not in eg pop up box i initially figured)?
Is the 3 condition alternative a big change to the current code? I expect this to be the last step, so if i would open a new thread its gonna be a short one. Should i post the existing code on the first post of a new thread then?

Thanks again for your thoughts
 
Upvote 0
You started out asking for the row #s that changed from '0' to '1'. Then you changed it to wanting the row #s that changed from '0' to '1' or '0' to '-1'. Then you changed it to instead of the row #s that changed, record the asset name.

Now you are wanting the asset name as well as monitor 3 different columns for changes. So the current ask is nothing like the original ask. Thus a new thread should be started. If you do that you will get more people looking at the new thread to try and assist you.

Short threads are, most of the time, better than long ones. People are more apt to read through a short thread than a very long thread that spans numerous pages.

You can post the current code that works for you in a new thread to show people what you are currently working with, sure.
 
Upvote 0
johnnyL said:
@Valentino when you start a new thread about your latest request that you made here, let us know.
Click to expand...
Hi Johnny, i'm still testing the current version and adjusting from 3 to 1 columns. If i manage, i will report here that this thread can be succesfully closed. Otherwise i will start new thread.

Thx,
Valentino
 
Upvote 0
johnnyL said:
Ok, thanks for the update
Click to expand...
Johnny,

as mentioned, i tested the solution and with some modifications elsewhere in my workbook this code works very well to alert me to a change in the conditions.
It is a different approach to what i described in the opening post, but alternative which works quite well.
Conclusion is that alerts can be generated by a macro, and that alternative solutions can sometimes work well.
Many thanks for your efforts, much appreciated !

Valentino
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
VBA Queries refresh and Save data as TXT
Replies
2
Views
451
Artik
Artik
A
  • Question Question
Office script in Excel instead of VBA
Replies
3
Views
331
6StringJazzer
6StringJazzer
J
  • Question Question
Split worksheets into separate excels AND only save those cells with data in them.
Replies
4
Views
390
mumps
mumps
A
  • Solved
Temporary sheet view not working
Replies
2
Views
100
ausswe
A
C
  • Question Question
Converting all of my Excel VBA library standard modules to class modules?
Replies
5
Views
403
RoryA
RoryA

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top