Excel MsgBox with VBA for multiple linked range

Question

I need some help with an excel problem.

It is a combination of the two problems below:

1) Excel - Popup message with sound alert when cell value meets certain criteria

2) VBA code to show Message Box popup if the formula in the target cell exceeds a certain value

In Sheet1, I have a range of products and sales figure. Example: Sheet1

In Sheet2, I have multiple columns of sumif() functions. Example: Sheet2. It contains a column of names in (A:A) and data in (B:B) & (C:C) which are linked to cells in other sheets. I would like a pop up notification saying ("Text in column A" sold is > 20) when the value of any cell in column B exceeds 20 or column C exceeds 40.

For example: If one of the cell value in column "B" gets updated to 33 (which is >20) and the corresponding cell value in column "A" contains text as "Charlie", excel sheet should popup message saying "Charlie sold is > 20".

The below VBA code accomplishes this IF it was raw data. However, it does not work when the cells are linked to data from other sheets, as in the case of this workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.column = 2 and target.value > 1 Then
       MsgBox target.offset(0,-1).text & " sold is > 20"
   End If
End Sub

This alternative code works with data linked from other sheets, however it only applies to a specific cell, not an entire column.

Private Sub Worksheet_Calculate()

If Sheets("Sheet2").Range("B2").Value > 20 Then
    MsgBox "B2 is >20", vbOKOnly
End If

End Sub

What I'm trying to achieve is this: As I input my raw data in Sheet1, the figures in Sheet2 column(B:B) and column(C:C) get updated. Once any of the cells in Sheet2 column(B:B) exceed 20 or column(C:C) exceed 40, there will be a popup notification that links back to column A text such as MsgBox target.offset(0,-1).text & " sold is > 20". Is there a way to combine the above two codes to achieve this? Any alternative solutions are welcome too, thank you!

kolcinx kolcinx · Accepted Answer · 2017-01-05T12:04:05

Compare all the Sums in Summary table

Private Sub Worksheet_Calculate()

    Dim RangeToCheck As Range
    Dim Cell As Range
    Set RangeToCheck = Sheets("Sheet2").Range("B2:B5") 'See comment #1

    For Each Cell In RangeToCheck.Cells
        With Cell
            If .Value2 > 20 Then
                MsgBox "Product: " & .Offset(columnoffset:=-1).Value2 & _
                " in cell: " & .Address & " is " & .Value2 & ">20", vbOKOnly
            End If
        End With
    Next

End Sub

Comments

I recommend turning the range on Sheet2 to Excel table and using ListObject and Listcolumns instead.
Off topic hint: You can also use some counter, put the range into an VBA array and loop through array, it will be faster, than refering to sheet cell one after another. See Writing efficient VBA UDFs (Part 1).

Excel MsgBox with VBA for multiple linked range

1 Answers

Compare all the Sums in Summary table

Comments