VBA to check field value during closing

[Rebel]

Hello,

I apologize if this has been asked anywhere, but I could not find an answer to it. I have to say that I am not familiar with VBA right away, so please do not expect much from me. I have coded in C, C++, JAVA etc., but VBA I am not familiar with.

Anyway, here is what I am trying to find out:
Is it possible to write a VBA script that will check a value in a cell and give you a popup message during spreadsheet closing where it will display the value?
Basically I need to check a cell value and display it and give maybe 2 different messages to the user e.g. If the cell value is >=1 "Cell value is such and such" and if the value =0 "This is a new message". And once I click on the OK button, it can close a spreadsheet. I am basically trying to remind the user about the value before they leave the spreadsheet.

Is this (or something similar) possible in Excel using the VBA? I am open to any other suggestions, but I don't know how sufficient a macro would be in this case because I would like to make it automatic. I could maybe use a macro and tie it to a button that will do more things at once, but I think script would be cleaner if that is possible.


Thanks so much guys, you have a great problem/solution blog here, keep up the good work.

 

[JoeMo]

Yes, it's possible to do what you want with a before_close event trigger.
What is the worksheet name and cell address that contains the check value?

 

[Rebel]

Yes, it's possible to do what you want with a before_close event trigger.
What is the worksheet name and cell address that contains the check value?

Thanks for the reply. I think the worksheet is simply called "Sheet 1" and the cell is something like D50.

 

[JoeMo]

Here's some code that you can use. The pop-up messages can be modified to suit and so can the cell address and sheet name. This code goes in a module for Thisworkbook. To load it do the folllowing:

1. open the workbook
2. press alt + F11 key
3. in the VB editor window that opens locate your workbook in the left portion of the window and double click on the ThisWorkbook icon
4. copy the code below and paste it into the white space in the right portion of the VB window
5. close the VB window
6. save the workbook - if you are using Excel 2003 or earlier save with the usual .xls extension. if 2007 or later, save with a .xlsm extension (not .xlsx)
7. close the workbook
8. when you open the workbook be sure to enable macros or the code will not work

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim myVal As Double
With Sheets("sheet1")
    If Not IsEmpty(Range("D50")) Then
        myVal = Range("D50").Value
    Else
        Exit Sub
    End If
End With
Select Case myVal
    Case Is >= 1
    MsgBox "Cell " & Range("D50").Address & " value is such and such"
    Case 0
    MsgBox "This is a new message"
    Case Else
    'do nothing
End Select
End Sub

 

[Rebel]

Thank you very much JoeMo for your prompt replies! I will give it a shot tomorrow and see how it works. I am illiterate when it comes to VBA (mainly because I don't use it ever, but need it now for Excel) so this is a huge help.

 

[JoeMo]

OK, post back if you hit a snag.

 

[Rebel]

JoeMo thanks for the help. This was a great start, so I just expanded on it and added other stuff. One of the things I have changed was changing .Address to .Value to display the value instead of the cell. Anyway, thanks again.

MsgBox "There are " & Range("D50").Value & " items remaining"