View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.misc
Patrick Riley Patrick Riley is offline
external usenet poster
 
Posts: 34
Default Require input in a cell before saving file

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