CODEWITHSUNDEEP
excelvba
Jojo
Jojo

Reputation: 13

Excel Message box when cell value more than 100

I have a report that have 2 rows, the first one is " Item " and the second is " Weight ".

the " Weight " will calculated and if the total summary is more than 100 % then Excel will show Warning Box.

The form

Can you guys help me to find a way to make it in Excel?

this is script I use before:

Private Sub Worksheet_Change(ByVal Target As Range)
    Set Target = Me.Range("A2:B10")
    If Target.Value > 100 Then
        MsgBox "You exceeded 100"
    End If
End Sub

Upvotes: 0

Views: 2280

Answers (2)

Variatus
Variatus

Reputation: 14383

Perhaps this is what you had in mind?

Private Sub Worksheet_Change(ByVal Target As Range)

    With Target
        If Not Application.Intersect(Range("A2:B10"), Target) Is Nothing Then
            If .Value > 100 Then
                MsgBox "You exceeded 100"
            End If
        End If
    End With
End Sub

The above code will only run if one of the cells in the Range(A2:B10) was changed. It will look at the changed cell (that is Target) and issue a message if its value is greater than 100.

Referring to your comment, the variation of the above code given below would draw a total of the cells in the monitored range, issue a message if that total exceeds 1 and return the selection to the faulty cell. Note that percentages are expressed divided by 100. Therefore the value 1 would be displayed as 100%.

Private Sub Worksheet_Change(ByVal Target As Range)
    Const RangeAddress As String = "A2:B10"
    Dim Ttl As Double

    With Target
        If Not Application.Intersect(Range(RangeAddress), Target) Is Nothing Then
            Ttl = Application.WorksheetFunction.Sum(RangeAddress)
            If Ttl > 1 Then
                MsgBox "You exceeded 100%"
                .Select
            End If
        End If
    End With
End Sub

Upvotes: 0

Scott Holtzman
Scott Holtzman

Reputation: 27259

the " Weight " will calculated

so you can do 1 of two things, 1) base the worksheet_change event on the cell that will be changed to affect the calculation and check the result of the calculation:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.Range("A2:B10") is nothing Then
        If Me.Range("A2").Value > 100 Then 
            MsgBox "You exceeded 100"
       End If
    End If

End Sub

or 2) you can use the Calculate Event, this will fire each time the sheet calculates:

Private Sub Worksheet_Calculate()

    If Range("A2").Value > 100 Then 
        MsgBox "You exceeded 100"
    End If

End Sub

Upvotes: 1

Related Questions