Message Box

[grambo]

I've seen a couple similar but don't quite work. I have a budget spreadsheet that I want to have a message box popup when you open the workbook that indicates if I have any negative budget categories.

So I have categories I2:AB2 some positive, some negative, and some at the end are blank. I also have 2 worksheets, one checking, one savings, they are copies. I tried a Range("I2:AB2") < 0 but I get a type mismatch I'm guessing becuase of the blank cells.

Basically, if any of those cells on the 2 worksheets are negative a message pops up that I'm over budget in one or more categories. I don't want it to popup every time I enter/change data just when I open (and when closing if possible/easy).

Thanks

 

[JoeMo]

I've seen a couple similar but don't quite work. I have a budget spreadsheet that I want to have a message box popup when you open the workbook that indicates if I have any negative budget categories.

So I have categories I2:AB2 some positive, some negative, and some at the end are blank. I also have 2 worksheets, one checking, one savings, they are copies. I tried a Range("I2:AB2") < 0 but I get a type mismatch I'm guessing becuase of the blank cells.

Basically, if any of those cells on the 2 worksheets are negative a message pops up that I'm over budget in one or more categories. I don't want it to popup every time I enter/change data just when I open (and when closing if possible/easy).

Thanks

Put the code below into the ThisWorkBook module for both the Open and BeforeClose events. Code goes between the Sub and End Sub statements.

Dim rng As Range
Set rng = Range("I2:AB2")
For Each cel In rng
    If cel.Value < 0 Then
        MsgBox "Over Budget"
        Exit For
    End If
Next cel

 

[AlphaFrog]

Or something like this...

Private Sub Workbook_Open()
    If Application.Min(Sheets("Checking").Range("I2:AB2")) < 0 Or _
        Application.Min(Sheets("Savings").Range("I2:AB2")) < 0 Then
            MsgBox "Over budget in one or more categories"
    End If
End Sub

        
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Application.Min(Sheets("Checking").Range("I2:AB2")) < 0 Or _
        Application.Min(Sheets("Savings").Range("I2:AB2")) < 0 Then
            MsgBox "Over budget in one or more categories"
    End If
End Sub

 

[HalfAce]

Hi grambo, welcome to the board.
For the quick & clean go with AlphaFrog's suggestion. It's about as quick & clean as it's gonna get.

Just for fun, give these a try. (You can only have one set of these open & close events active at a time though.)

Private Sub Workbook_Open()
Dim CatRng As Range, Catc As Range, _
    Msg1 As String, Msg2 As String, _
    obCatName As String, ShtToChk As String, _
    obCount As Long
Msg1 = "You are over budget in the catagory: " & vbCrLf
Msg2 = "You are over budget in the following catagories: " & vbCrLf
obCount = 0
obCatName = ""
ShtToChk = "Checking"
StartChecking:
With Sheets(ShtToChk)
    Set CatRng = .Range("I2:AB2")
    For Each Cat In CatRng
        If Cat.Value < 0 Then
           obCount = obCount + 1
           If Len(obCatName) = 0 Then
              obCatName = Cat.Offset(-1).Value
            Else
                obCatName = obCatName & ", " & Cat.Offset(-1).Value
            End If
        End If
    Next Cat
End With
Select Case obCount
    Case 0
        MsgBox "All is cool on the " & ShtToChk & " sheet. " & vbGrLf & "(Go ahead and go out to dinner!)"
    Case 1
        MsgBox Msg1 & obCatName & " on the " & ShtToChk & " sheet."
    Case Is > 1
        MsgBox Msg2 & obCatName & " on the " & ShtToChk & " sheet."
End Select
If ShtToChk = "Checking" Then _
    ShtToChk = "Savings": obCount = 0: obCatName = "": GoTo StartChecking
End Sub
 
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim CatRng As Range, Catc As Range, _
    Msg1 As String, Msg2 As String, _
    obCatName As String, ShtToChk As String, _
    obCount As Long
Msg1 = "You are over budget in the catagory: " & vbCrLf
Msg2 = "You are over budget in the following catagories: " & vbCrLf
obCount = 0
obCatName = ""
ShtToChk = "Checking"
StartChecking:
With Sheets(ShtToChk)
    Set CatRng = .Range("I2:AB2")
    For Each Cat In CatRng
        If Cat.Value < 0 Then
           obCount = obCount + 1
           If Len(obCatName) = 0 Then
              obCatName = Cat.Offset(-1).Value
            Else
                obCatName = obCatName & ", " & Cat.Offset(-1).Value
            End If
        End If
    Next Cat
End With
Select Case obCount
    Case 0
        MsgBox "All is cool on the " & ShtToChk & " sheet. " & vbGrLf & "(Go ahead and go out to dinner!)"
    Case 1
        MsgBox Msg1 & obCatName & " on the " & ShtToChk & " sheet."
    Case Is > 1
        MsgBox Msg2 & obCatName & " on the " & ShtToChk & " sheet."
End Select
If ShtToChk = "Checking" Then _
    ShtToChk = "Savings": obCount = 0: obCatName = "": GoTo StartChecking
End Sub

Hope it helps.