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
"cskgg " wrote in message
...
| 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

--
Message posted from http://www.ExcelForum.com

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

-----Original Message-----
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 the
workbook, the workbook saved without the prompt - i.e.
NOTHING
HAPPENS!! (Incidentally, cell A1 was "").
Neither can I run the code by the normal RUN command in
the VB editor
(it asks for which macro to run) nor does the code run
in the
background when I try to save the file. I am a bit
stumped. Appreciate
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 is
no
reason for external modules to call it. But it works
just as well
without
the Private.

--
Jim Rech
Excel MVP
"cskgg " wrote in
message
...
| 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. 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



---
Message posted from http://www.ExcelForum.com/

.

Dear all,

Thanks a ton. It works now.

Cheers to the forum.
cskgg


---
Message posted from http://www.ExcelForum.com/