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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,429
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
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
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,455
Members
417,025
Latest member
MusterDuster

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
Top