CODEWITHSUNDEEP
excelexcel-2007vba
ackdaddy
ackdaddy

Reputation: 131

Prompting a Msg Box when data in an Excel cell range updates

I have searched high an low for this but cannot get it to work for my entire range. And this is my first post, so be gentle.

I have an algorithm that analyzes the time that certain products should move from one production phase to the next. It then provides the amount of resources needed to complete the next phase of production.

My problem is that when the resources exceed what is available I want a pop-up box to appear and warn the user; this should happen for all cells within the range. I have gotten this to work successfully, but only when cell "S7" exceeds the value. Any help will be greatly appreciated.

Code is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myRng As Range

    Set myRng = ThisWorkbook.Sheets("SMT 2").Range("S7:S26")

    For Each mycell In myRng

        If mycell.Value >= 16 Then sVar = _
        MsgBox("Will Enough Pre-Wave Resources be Available?", 4, "Attention!")

        If sVar = 7 Then
            Application.Undo
        End If

        Exit For
    Next
End Sub

Upvotes: 1

Views: 96

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149287

You don't need a loop. You can use Intersect to check if any cell in the range Range("A7:A26") is changed. Also note, the below code doesn't cater to scenario where then user pastes values >16 in that range.

Is this what you are trying?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myRng As Range
    Dim lRow As Long

    If Target.CountLarge > 1 Then Exit Sub

    On Error GoTo Whoa

    Set myRng = Range("A7:A26")

    Application.EnableEvents = False

    If Not Intersect(Target, myRng) Is Nothing Then
        lRow = Target.Row

        If Range("S" & lRow).Value >= 16 Then sVar = _
        MsgBox("Will Enough Pre-Wave Resources be Available?", 4, "Attention!")

        If sVar = 7 Then Application.Undo
    End If

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Upvotes: 2

Manuel Allenspach
Manuel Allenspach

Reputation: 12725

You want to go through all cells or just S7 to S26?

If you want to go through S7:S26, this is the code: (Exit For was at the wrong place)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myRng As Range
    Set myRng = ThisWorkbook.Sheets("SMT 2").Range("S7:S26")

    For Each mycell In myRng
        If mycell.Value >= 16 Then 
            sVar = MsgBox("Will Enough Pre-Wave Resources be Available?", 4, "Attention!")
            If sVar = 7 Then
                Application.Undo
                Exit For
            End If
        End if
    Next
End Sub

Upvotes: 1

Related Questions