Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
If IsEmpty(Sheets("Main").Range("E59").Value) Then
MsgBox "Cell E59 of sheet 'Main' must be filled in before " & _
"this file can be saved.", 16, "ERROR"
Cancel = True
End If
End Sub
Gord Dibben MS Excel MVP
On Tue, 25 Mar 2008 07:24:01 -0700, Patrick Riley
Otto:
Thanks for the explanation. I will use your method of right-clicking on the
icon to the left of "File" at the upper-left of the window to reach VBA
editor.
Using this method, I placed the following code into the ThisWorkbook module:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If IsEmpty(Sheets("Main").Range("E59").Value) Then
MsgBox "Cell E59 of sheet 'Main' must be filled in before "
this file can be saved.", 16, "ERROR"
Cancel = True
End If
End Sub
[NOTE: VBA automatically appends a double quote (") at the end of line 4,
following "must be filled in before". ]
...and yet I still can save the file with cell E59 being empty.
I appreciate yours and Dave's diligence in this matter. Any further
suggestions?
---Pat
:
Pat
Yes, there is. What you describe puts the code into the sheet module.
It must go into the workbook module. Otto
Dave:
I did place the code in ThisWorkbook (and curiously---remember, I'm not a
VBAer--- the code was then visible under each of the 3 worksheet modules
(Sheet1, Sheet2, Sheet3).
I did change Otto's "Sht Name" to "Main". I also tried "Sheet1".
AND I MAY BE CONFUSED ABOUT HOW TO PLACE CODE INTO VBA EDITOR .
I had been putting the code into the VBA editor by right-clicking on the
worksheet's tab and selecting View Code (I have Excel 2002). This is
different from the method suggested by Otto, which is to right-click on
the
Excel icon immediately to the left of the word "File" of the menu that
runs
across the top of the screen, and then selecting View Code. IS THERE A
DIFFERENCE BETWEEN THESE 2 METHODS?
:
And are you sure you put the code in the ThisWorkbook module?
(I would have thought that you would have changed Otto's code 'Sht Name'
to
'Main'.)
Patrick Riley wrote:
Dave:
Thanks for your interest. Cell E59 is in a worksheet which I have
named (on
its tab) "Main". In the VBA editor, it appears as "Sheet1 (Main)"
without
the quote marks.
P.S. I'm not a VBA programmer, so my hope is that any offered solutions
keep
the code simple.
:
Is the cell that needs to be filled in (E59) on a sheet named Sheet1
or Main?
Sheets("Main")
may need to be changed to
sheets("Sheet1")
And are you sure you put the code in the ThisWorkbook module?
Patrick Riley wrote:
Otto:
The "Good Morning, Pat" subroutine worked. But I still failed in
my main
goal.
First, I entered only the "Good Morning, Pat" sub in the VB editor,
and the
"Good Morning, Pat" text greeted me upon saving & re-opening the
file.
Then, I added the sub for requiring an entry in cell 59 before the
file can
be saved. Result: the "Good Morning, Pat" text appeared, but I was
still
able to save the file with cell E59 remaining blank.
Here is my edited version of your code that I used:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel
As
Boolean)
If IsEmpty(Sheets("Main").Range("E59").Value) Then
MsgBox "Cell E59 of sheet 'Sht Name' must be filled in
before "
this file can be saved.", 16, "ERROR"
Cancel = True
End If
End Sub
My only edit of your code was to replace "Sht Name" (in your
original code)
with "Sheet1". I also tried using "Main" (the worksheet tab name),
but
neither forced me to enter data in E59 before saving.
:
Pat
That should not happen and indicates that you are doing
something I
don't know. I have 2002 also and it works for me. Post back and
provide a
detail step-by-step explanation of what you are doing to place
the code into
the workbook module. Pretend that you are talking to someone who
knows
nothing about what you are doing.
One thing that occurs to me is that you may be opening the file
with macros
disabled. To check this, paste this little macro into the
workbook module,
then save the file and close the file and open the file. You
should see a
message box that says "Good morning, Pat".
Sub Workbook_Open
MsgBox "Good morning, Pat"
End Sub
HTH Otto
message
Otto:
I tried this, but it did not work. I placed the code under
ThisWorkbook
(incidentally, the code then automatically showed up under each
of the 3
worksheets in the workbook).
I am using Excel 2002. ( I have protected the wookbook and each
worksheet,
although I think this should make no difference---I tried the
code with
protection both off and on).
For "Sht Name" I substited "Sheet1" and when that failed, I
tried
substituting "Main" (the name of the worksheet's tab) but that
did not
work
either.
Any further suggestion?
:
Patrick
You will need a Before_Save event macro for this. The
following
macro
will do it for you. This macro does the following when a SAVE
command is
issued by the user:
Checks cell E59 for content.
If the cell is occupied, it will allow the save to continue.
If the cell is empty it will display a message box advising
the user that
the file cannot be saved unless cell E59 is filled in. Then
it will
cancel
the SAVE command.
Note that I assumed your sheet is named "Sht Name" and wrote
that name
into
the code. Change that as needed.
Note that this is a workbook event macro and must be placed in
the
workbook
module. In all versions of Excel before 2007, that module can
be
accessed
by right-clicking on the Excel icon that is immediately to the
left of
the
word "File" in the menu that runs across the top of the
screen, selecting
View Code and pasting this macro into the displayed module.
Perhaps
someone
can step in here and tell you how to access that module in
2007. "X" out
of
the module to return to your worksheet. Come back if you need
more. HTH
Otto
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean,
Cancel As
Boolean)
If IsEmpty(Sheets("Sht Name").Range("E59").Value) Then
MsgBox "Cell E59 of sheet 'Sht Name' must be
filled in before
this file can be saved.", 16, "ERROR"
Cancel = True
End If
End Sub
in message
I want to require the user to enter his/her name in a cell
(E59) before
the
user can save the file.
I tried using Data Validation where I specified Text Length
between 1
and
40, and left blank the check-box for "Ignore Blank". Nope.
I never
programmed in VBA, so I hope there is a simple solution (I
might be OK
with
some simple VBA code).
---Pat
