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

Have a look in the ThisWorkbook macro module for beforesave

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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

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

Don:
I tried this, but it did not work. I placed the code under ThisWorkbook
(incidentally, it automatically showed up in each of the worksheets).
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). any further suggestion?

"Don Guillett" wrote:

Have a look in the ThisWorkbook macro module for beforesave

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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

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

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

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

Pat
Substitute this little macro for the "Good Morning" macro. Then save the
file, close the file and open the file and see if cell E59 in sheet "Main"
is the active cell.
Sub Workbook_Open()
Sheets("Main").Select
Range("E59").Select
End Sub
I'm assuming that you have a sheet named "Main". HTH Otto


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

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

Otto:
I followed your instructions. Yes, E59 was the active cell.
(And Yes, the worksheet in question with cell E59 is Sheet1 which I have
named "Main".)

"Otto Moehrbach" wrote:

Pat
Substitute this little macro for the "Good Morning" macro. Then save the
file, close the file and open the file and see if cell E59 in sheet "Main"
is the active cell.
Sub Workbook_Open()
Sheets("Main").Select
Range("E59").Select
End Sub
I'm assuming that you have a sheet named "Main". HTH Otto


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

Pat
Make a small file and make sure that this small file does exactly what
you have been describing. Exactly. Place the macro(s) we have been talking
about where you think they should be in this little file. Then send me that
file. My email address is . Remove the
"extra" from this address. HTH Otto
"Patrick Riley" wrote in message
...
Otto:
I followed your instructions. Yes, E59 was the active cell.
(And Yes, the worksheet in question with cell E59 is Sheet1 which I have
named "Main".)

"Otto Moehrbach" wrote:

Pat
Substitute this little macro for the "Good Morning" macro. Then save
the
file, close the file and open the file and see if cell E59 in sheet
"Main"
is the active cell.
Sub Workbook_Open()
Sheets("Main").Select
Range("E59").Select
End Sub
I'm assuming that you have a sheet named "Main". HTH Otto


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

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

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

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

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

Otto:
I never responded to your request for a step-by-step description of my
actions. Here it is:

1. I block-copy the code you sent me, substituting "Main" for the 2
occurrences of "Sht name", thus:
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

2. I right-click on the Excel icon to the left of "File" in upper-left of
window.
3. I select "View code".
4. A VBA window opens up, with 2 panes.
5. In the left pane, I select "ThisWorkbook".
6. In the right pane, I paste in the code referenced in step 1.
7. I left-click on the boxed red X at upper right of VBA window to close it.
It closes.
8. I select File, Save. The file is saved.
9. I close the file. It closes.
10. I re-open the file.
11. I close the file. It closes.

I never see the prompt to fill in cell E59. I am not prevented from saving
the file, even though cell E59 is still blank (empty).

---Pat


"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

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

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