VBA code to display a Message Box popup if the cell value exceeds the value of another

Question

I have a script in Excel that requires some vba code. I am a relative newbie and have come to a standstill trying to find a solution.

Say, for example, the user enters a numerical value in cell A1.

Then they must also enter values ​​in cells A5, A6, A7 and A8.

The total amount of this amount is displayed in cell A9 using the generic SUM excel function.

None of the cells A5: A8 can be left empty, although entering zero ('0') is permissible.

The value of A9 can be less, equal, but not exceed the value in A1.

If A9 exceeds A1, an error message should appear to warn them that it is.

Alpha characters cannot be entered. An error message will appear to warn them, if any.

Entering numbers must be in the range from 0 to 9999.999. An error message will appear to warn them if they do not.

I was given a piece of vba code (below) that I use for a similar purpose, which works very well. However, I cannot figure out how to include code that will identify and return an error message if the value in A9 exceeds A1. This is what I tried to do, but I know that it is wrong! The code is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Not Intersect(Target, Range("A9")) Is Nothing Then
    Application.EnableEvents = False
    For Each c In Intersect(Target, Range("A9"))
        If IsEmpty(c) Then
            Application.EnableEvents = True
            Exit Sub
        End If
        If Not VarType(c.Value2) = vbDouble Or c.Value < 0 Or c.Value > 9999999 Then
            MsgBox "Entry in cell " & c.Address(0, 0) & " must be a number from 0 and 9,999,999"
            Application.Undo
        ElseIf WorksheetFunction.Count(Range("A9")) = 1 And _
            WorksheetFunction.Sum(Range("A9")) > Range("A1").Value Then
            MsgBox "The sum of A99 cannot exceed A1 when all entries are completed"
            Application.Undo
        End If

If anyone could help me with this, he would really appreciate it!

Feces

Answer 1

, Worksheet_Change A1 A5: A8, A9. A5: A8 , A1.

Intersect, Case, .

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1, A5:A8")) Is Nothing Then
        On Error GoTo bm_Safe_exit
        Application.EnableEvents = False
        Dim c As Range
        For Each c In Intersect(Target, Range("A1, A5:A8"))
            If Application.Count(Range("A1, A5:A8")) < 5 Then
                Range("A9") = vbNullString
            Else
                Range("A9").Formula = "=SUM(A5:A8)"
                Select Case Range("A9").Value2
                    Case Is > Range("A1").Value2
                        MsgBox "The sum of A9 cannot exceed A1 when all entries are completed"
                        Range("A9", c).ClearContents
                        GoTo bm_Safe_exit
                    Case Is < 0, Is >= 10 ^ 7
                        MsgBox "Entry in cell " & c.Address(0, 0) & " must be a number from 0 and 9,999,999"
                        Range("A9", c).ClearContents
                        GoTo bm_Safe_exit
                    Case Else
                        'do nothing - A9 is oh-key-doh-key
                End Select
            End If
        Next c
    End If
bm_Safe_exit:
    Application.EnableEvents = True
End Sub

Answer 2

:

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim rLook As Range, v1 As Variant, v5 As Variant
   Dim v6 As Variant, v7 As Variant, v8 As Variant
   Dim bad As Boolean
   Set rLook = Range("A1, A5:A8")

   If Intersect(Target, rLook) Is Nothing Then Exit Sub
   v1 = Range("A1").Value
   v5 = Range("A5").Value
   v6 = Range("A6").Value
   v7 = Range("A7").Value
   v8 = Range("A8").Value
   If v1 = "" Or v5 = "" Or v6 = "" Or v7 = "" Or v8 = "" Then Exit Sub

   bad = False
   If Not IsNumeric(v1) Then bad = True
   If Not IsNumeric(v5) Then bad = True
   If Not IsNumeric(v6) Then bad = True
   If Not IsNumeric(v7) Then bad = True
   If Not IsNumeric(v8) Then bad = True
   If bad Then
      MsgBox "non-numeric data"
      Exit Sub
   End If

   If v1 < 0 Or v1 > 9999999 Then bad = True
   If v5 < 0 Or v1 > 9999999 Then bad = True
   If v6 < 0 Or v1 > 9999999 Then bad = True
   If v7 < 0 Or v1 > 9999999 Then bad = True
   If v8 < 0 Or v1 > 9999999 Then bad = True
   If bad Then
      MsgBox "data out of bounds"
      Exit Sub
   End If

   If Range("A9").Value > v1 Then
      MsgBox "sum exceeds the value in A1"
   End If

End Sub

, A1 A5 A8.

, A9