Prompt before saving

C

cskgg

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.
cskg
 
J

Jim Rech

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 is no
reason for external modules to call it. But it works just as well 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
|
|
| ---
|
|
 
M

Mike Fogleman

This is a Private Sub, not a Function. There is a difference. Private means
it can not be called to run from other macros. In this case the code belongs
to ThisWorkbook and should be placed there, not in a standard code module.
This Sub should work for you if modified to test for the account number. You
didn't specify what cell the number should be in or how we will know if it
matches correctly. Assuming the cell you want them to put the account number
in is Sheet1 A1 and you have put the correct account number somewhere 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
 
Top