Prompt before saving

[cskgg]

Dear Jim Rech and Mike F,

Thank you very much for your prompt reply and information. Problem is
I pasted the code in "ThisWorkbook" and then tried to save th
workbook, the workbook saved without the prompt - i.e. NOTHIN
HAPPENS!! (Incidentally, cell A1 was "").
Neither can I run the code by the normal RUN command in the VB edito
(it asks for which macro to run) nor does the code run in th
background when I try to save the file. I am a bit stumped. Appreciat
your help.

Thanks/Cheers
cskgg


Go to the ThisWorkbook module of your workbook and paste in this code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If Sheet1.Range("A1").Value = "" Then
MsgBox "Cannot save without an account code"
Cancel = True
End If
End Sub

Private just means this sub cannot be seen from outside the module
This is
the default since this sub handles event of this workbook and there i
no
reason for external modules to call it. But it works just as wel
without
the Private.

--
Jim Rech
Excel MVP
| Hello all,
|
| Need help in the following:
|
| I want the user to enter an ACCOUNT CODE in a workbook BEFORE they
| can save it. That is, whenever the user clicks on the SAVE button
| or clicks on File > save, the system must check if ACCOUNT CODE is
| present in the specified cell, otherwise it should prompt the user
| input the account code first before saving.
| I checked the VB-Help and the code it showed is a PRIVATE FUNCTION.
| I cannot use it in its present form.
|
| Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, _
| ByVal SaveAsUI As Boolean, Cancel as Boolean)
| a = MsgBox("Do you really want to save the workbook?", vbYesNo)
| If a = vbNo Then Cancel = True
| End Sub
|
| (by the way, when can we use PRIVATE FUNCTIONS??)
|
| Any suggestions ???
|
| Many thanks/Cheers.
| cskgg


This is a Private Sub, not a Function. There is a difference. Privat
means
it can not be called to run from other macros. In this case the cod
belongs
to ThisWorkbook and should be placed there, not in a standard cod
module.
This Sub should work for you if modified to test for the accoun
number. You
didn't specify what cell the number should be in or how we will know i
it
matches correctly. Assuming the cell you want them to put the accoun
number
in is Sheet1 A1 and you have put the correct account number somewher
way
off the sheet at Sheet1 AZ 10000:

Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, _
ByVal SaveAsUI As Boolean, Cancel as Boolean)
If Sheet1.Range("A1").Value = Sheet1.Range("AZ 10000").Value Then
Exit Sub
Else
a = MsgBox("Put the Account Number in cell A1?", vbOK)
If a = vbOK Then Cancel = True
End Sub

Mike F
:confused

 

[Jason Morin]

I think it's a word wrapping issue. When you paste the
code into the module window, make sure the first line is
together without any additional spaces. If it turns red,
something is amiss.

HTH
Jason
Atlanta, GA

 

[cskgg]

Dear all,

Thanks a ton. It works now.

Cheers to the forum.
cskgg