Message Box

grambo

New Member
Joined
Jul 17, 2010
Messages
2
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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.
Code:
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
 
Upvote 0
Or something like this...

Code:
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
 
Upvote 0
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.)
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,218,795
Messages
6,144,527
Members
450,550
Latest member
Melanie_Annies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top