Require input in a cell before saving file

Gord:
IT WORKED THIS TIME!!. Thanks so much for sweating this out with me! Don't
know what I did differently last time. Maybe I forgot to remove the "" from
in front of each line of code.

BUT...By testing this on officemates' machines, I have realized that I have
another hurdle to clear. (The file is meant to be used repeatedly by everyone
in the department, with the user's name to appear on a printed hard copy; it
is Read-only, so each file gets saved under a new filename, and the content
contains the user's name.) Their Excel security level is set to "High" (mine
is "Low"), so running the macro-embedded file on their machines produces an
error message that macros are disabled because of the "High" setting.
The error message posits 2 ways around this: 1) Change the security level,
or 2) Digitally sign and verify the macro as safe.
I suspect our IT Dept. set "High" as the default and wants it that way, so
changing the security level is not an option. So, I confess that I do not
know what a digital signature means or involves (I'm not a programmer, just
the least computer-phobic person in the department :-)

Can I trouble you for one further assist, to point me toward an explanation
of what a digital signature involves, and how to sign and verify a macro?
And how is it that other users can know a macro is digitally signed and
verified as safe?

I know this goes a little beyond my original post. Anyway, thanks again for
bearing with me. I learned a lot from this discussion.
---Pat

"Gord Dibben" wrote:

On which line?

Works for me in Excel 2003.

Did you copy/paste exact code from my post?


Gord

On Tue, 25 Mar 2008 12:05:02 -0700, Patrick Riley
wrote:

Gord:
Thank you for your contribution. I ran the code and got this response:

Compile error:
Expected: type name

What do you think happened?
----------------------------------
"Gord Dibben" wrote:

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
wrote:

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

"Otto Moehrbach" wrote:

Pat
Yes, there is. What you describe puts the code into the sheet module.
It must go into the workbook module. Otto

"Patrick Riley" wrote in message
...
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?

"Dave Peterson" wrote:

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.

"Dave Peterson" wrote:

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.

"Otto Moehrbach" wrote:

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
"Patrick Riley" wrote in
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?

"Otto Moehrbach" wrote:

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
"Patrick Riley" wrote
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







--

Dave Peterson


--

Dave Peterson

Gord:
Ignore my previous post seeking information on Excel digital signature.
I have read Excel's Help entry for digital signature, and it answered my
questions.
As I noted in that previous post, your code worked.
Again, I appreciate the help and information that you provided.
---Pat

"Gord Dibben" wrote:

On which line?

Works for me in Excel 2003.

Did you copy/paste exact code from my post?


Gord

On Tue, 25 Mar 2008 12:05:02 -0700, Patrick Riley
wrote:

Gord:
Thank you for your contribution. I ran the code and got this response:

Compile error:
Expected: type name

What do you think happened?
----------------------------------
"Gord Dibben" wrote:

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
wrote:

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

"Otto Moehrbach" wrote:

Pat
Yes, there is. What you describe puts the code into the sheet module.
It must go into the workbook module. Otto

"Patrick Riley" wrote in message
...
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?

"Dave Peterson" wrote:

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.

"Dave Peterson" wrote:

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.

"Otto Moehrbach" wrote:

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
"Patrick Riley" wrote in
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?

"Otto Moehrbach" wrote:

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
"Patrick Riley" wrote
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







--

Dave Peterson


--

Dave Peterson