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

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

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