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

Question

I have a scenario in Excel which requires some vba code. I'm a relative newbie and have reached a dead end in trying to find a solution.

Say for instance, a user inputs a numerical value in cell A1.

They must then also enter values in cells A5,A6,A7 and A8.

The total of this sum is displayed in cell A9, using a generic excel SUM function.

None of the cells A5:A8 can be left blank, although a zero ('0') input is acceptable.

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

If A9 exceeds A1, an error message must pop up to alert them that this is the case.

Alpha characters cannot be input. An error message pops up to alert them if they are.

Numbers input must range between 0 and 9,999,999. An error message pops up to alert them if they don't.

I was given a piece of vba code (below) which I use for a similar purpose which works really well. I cannot however, figure out how to incorporate the code which will identify and return an error message if the value in A9 exceeds A1.This what I tried to do, but I know it’s 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, it would be very much appreciated!!

Cal

Answer 1

This should meet your needs:

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

The macro is triggered when the user completes inputs to A1 and A5 through A8.

I assume that the sum formula is already in cell A9

Answer 2

It seems to me that your Worksheet_Change event macro should be dealing with changes to A1 and A5:A8, not A9. If the values in A5:A8 meet criteria, you can then check their total against A1.

After adjusting the Intersect method(s), I've used a Select Case statement to organize the various logic conditions.

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