Thanks to some generous help from users of this site, I, with no Visual Basic
knowledge, am using some VB code that requires the user to type his/her name
into a given cell before the file can be saved, since the name must appear on
the printed version. The user-name field is intentionally left blank. Upon
attempting to save, the code presents a message to type in the name, if the
name-field is still blank.
Well, CATCH-22. This very code is preventing me from leaving the name field
blank when I try to save the file in final form, ready for enduser use.
The code I am using is:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
If IsEmpty(Sheets("Main").Range("E59").Value) Then
MsgBox "You must type in your name before " & _
"this file can be saved.", 16, "ERROR"
Cancel = True
End If
End Sub

Any suggestions?
---Pat Riley

Why not just have an input box that will populate the cell?

If IsEmpty(Sheets("Main").Range("E59").Value) _
Then
Range("E59").Value = InputBox("You must type in your name before " &
_
"this file can be saved.", "Enter Name.")
Else
End If


HTH,
Paul

--

"Patrick Riley" <(E-Mail Removed)> wrote in message
news:C0FC2015-CD95-43EB-A2A4-(E-Mail Removed)...
> Thanks to some generous help from users of this site, I, with no Visual
> Basic
> knowledge, am using some VB code that requires the user to type his/her
> name
> into a given cell before the file can be saved, since the name must appear
> on
> the printed version. The user-name field is intentionally left blank. Upon
> attempting to save, the code presents a message to type in the name, if
> the
> name-field is still blank.
> Well, CATCH-22. This very code is preventing me from leaving the name
> field
> blank when I try to save the file in final form, ready for enduser use.
> The code I am using is:
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
> Cancel As Boolean)
> If IsEmpty(Sheets("Main").Range("E59").Value) Then
> MsgBox "You must type in your name before " & _
> "this file can be saved.", 16, "ERROR"
> Cancel = True
> End If
> End Sub
>
> Any suggestions?
> ---Pat Riley

If you put this code in the ThisWorkbook code module, it will clear the E59
cell for each user as the workbook opens.

Private Sub Workbook_Open()
Range("E59").ClearContents
End Sub

Open the VB editor and right click the ThisWorkbook listed in the project
window. Click View Code and paste the above snippet into the code window.
Then you can enter something in E59 to get it to save the file and when the
workbook opens it will automatically clear that cell. If I recall your
original post correctly, you only want the name there for print out which
occurs after the save event. If I am correct then this addition should allow
you to enter something in E59 to save your prototype file, which will be
removed upon the next opening of the file.


"Patrick Riley" wrote:

> Thanks to some generous help from users of this site, I, with no Visual Basic
> knowledge, am using some VB code that requires the user to type his/her name
> into a given cell before the file can be saved, since the name must appear on
> the printed version. The user-name field is intentionally left blank. Upon
> attempting to save, the code presents a message to type in the name, if the
> name-field is still blank.
> Well, CATCH-22. This very code is preventing me from leaving the name field
> blank when I try to save the file in final form, ready for enduser use.
> The code I am using is:
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
> Cancel As Boolean)
> If IsEmpty(Sheets("Main").Range("E59").Value) Then
> MsgBox "You must type in your name before " & _
> "this file can be saved.", 16, "ERROR"
> Cancel = True
> End If
> End Sub
>
> Any suggestions?
> ---Pat Riley

Before you (as the developer, not the user) save the file, you can turn off
events.

Open the VBE
hit ctrl-g to see the immediate window
type this and hit enter:
application.enableevents = false

Then save your workbook--the _beforesave event won't fire.

Then back to the VBE and toggle the setting in the immediate window:
application.enableevents = true

====
You could also do other stuff, too. Maybe check the username?

if lcase(application.username) = lcase("Patrick Riley") then
'do nothing, let it save...
'or clear that cell!
me.sheets("Main").range("e59").value = ""
else
If IsEmpty(me.Sheets("Main").Range("E59").Value) Then
MsgBox "You must type in your name before " & _
"this file can be saved.", 16, "ERROR"
Cancel = True
End If
end if



Patrick Riley wrote:
>
> Thanks to some generous help from users of this site, I, with no Visual Basic
> knowledge, am using some VB code that requires the user to type his/her name
> into a given cell before the file can be saved, since the name must appear on
> the printed version. The user-name field is intentionally left blank. Upon
> attempting to save, the code presents a message to type in the name, if the
> name-field is still blank.
> Well, CATCH-22. This very code is preventing me from leaving the name field
> blank when I try to save the file in final form, ready for enduser use.
> The code I am using is:
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
> Cancel As Boolean)
> If IsEmpty(Sheets("Main").Range("E59").Value) Then
> MsgBox "You must type in your name before " & _
> "this file can be saved.", 16, "ERROR"
> Cancel = True
> End If
> End Sub
>
> Any suggestions?
> ---Pat Riley

--

Dave Peterson

JLGWhiz:

Thanks for your response.
I tried your code, and got an error msg:

Run-time error '1004':
Cannot change part of a merged cell.

E59 is a merged cell, consisting of cells originally E59 thru O59 inclusive.
When I hit the Debug button within the err-msg window, the VB editor window
opened, and line 2 of your code was highlighted.

Also, it is my intent that the user name remain in the saved file, in
addition to appearing on the hardcopy.

"JLGWhiz" wrote:

> If you put this code in the ThisWorkbook code module, it will clear the E59
> cell for each user as the workbook opens.
>
> Private Sub Workbook_Open()
> Range("E59").ClearContents
> End Sub
>
> Open the VB editor and right click the ThisWorkbook listed in the project
> window. Click View Code and paste the above snippet into the code window.
> Then you can enter something in E59 to get it to save the file and when the
> workbook opens it will automatically clear that cell. If I recall your
> original post correctly, you only want the name there for print out which
> occurs after the save event. If I am correct then this addition should allow
> you to enter something in E59 to save your prototype file, which will be
> removed upon the next opening of the file.
>
>
> "Patrick Riley" wrote:
>
> > Thanks to some generous help from users of this site, I, with no Visual Basic
> > knowledge, am using some VB code that requires the user to type his/her name
> > into a given cell before the file can be saved, since the name must appear on
> > the printed version. The user-name field is intentionally left blank. Upon
> > attempting to save, the code presents a message to type in the name, if the
> > name-field is still blank.
> > Well, CATCH-22. This very code is preventing me from leaving the name field
> > blank when I try to save the file in final form, ready for enduser use.
> > The code I am using is:
> >
> > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
> > Cancel As Boolean)
> > If IsEmpty(Sheets("Main").Range("E59").Value) Then
> > MsgBox "You must type in your name before " & _
> > "this file can be saved.", 16, "ERROR"
> > Cancel = True
> > End If
> > End Sub
> >
> > Any suggestions?
> > ---Pat Riley

Paul:

Thanks for taking the time to respond.
I deleted existing VB code, and used your code. After inserting your code,
nothing happened:
I could save file with name-field blank. I did not see an input-box appear.
I saved file and re-opened, and got the same results: no input box, could
still save file with name-field blank.

"PCLIVE" wrote:

> Why not just have an input box that will populate the cell?
>
> If IsEmpty(Sheets("Main").Range("E59").Value) _
> Then
> Range("E59").Value = InputBox("You must type in your name before " &
> _
> "this file can be saved.", "Enter Name.")
> Else
> End If
>
>
> HTH,
> Paul
>
> --
>
> "Patrick Riley" <(E-Mail Removed)> wrote in message
> news:C0FC2015-CD95-43EB-A2A4-(E-Mail Removed)...
> > Thanks to some generous help from users of this site, I, with no Visual
> > Basic
> > knowledge, am using some VB code that requires the user to type his/her
> > name
> > into a given cell before the file can be saved, since the name must appear
> > on
> > the printed version. The user-name field is intentionally left blank. Upon
> > attempting to save, the code presents a message to type in the name, if
> > the
> > name-field is still blank.
> > Well, CATCH-22. This very code is preventing me from leaving the name
> > field
> > blank when I try to save the file in final form, ready for enduser use.
> > The code I am using is:
> >
> > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
> > Cancel As Boolean)
> > If IsEmpty(Sheets("Main").Range("E59").Value) Then
> > MsgBox "You must type in your name before " & _
> > "this file can be saved.", 16, "ERROR"
> > Cancel = True
> > End If
> > End Sub
> >
> > Any suggestions?
> > ---Pat Riley
>
>
>

Just to be sure, you are using:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
If IsEmpty(Sheets("Main").Range("E59").Value) _
Then
Range("E59").Value = InputBox("You must type in your name before " &
_
"this file can be saved.", "Enter Name.")
Else
End If
End Sub

Are you sure E59 is currently empty. The code works when I attempt saving.
But if there is a space or anything else in E59, no input box is displayed.
Also, I saw in another post where you were to disable events. Make sure
they are not currently disabled.

Regards.

--

"Patrick Riley" <(E-Mail Removed)> wrote in message
news:35CD6EB5-04EF-466A-BCB0-(E-Mail Removed)...
> Paul:
>
> Thanks for taking the time to respond.
> I deleted existing VB code, and used your code. After inserting your
> code,
> nothing happened:
> I could save file with name-field blank. I did not see an input-box
> appear.
> I saved file and re-opened, and got the same results: no input box, could
> still save file with name-field blank.
>
> "PCLIVE" wrote:
>
>> Why not just have an input box that will populate the cell?
>>
>> If IsEmpty(Sheets("Main").Range("E59").Value) _
>> Then
>> Range("E59").Value = InputBox("You must type in your name before
>> " &
>> _
>> "this file can be saved.", "Enter Name.")
>> Else
>> End If
>>
>>
>> HTH,
>> Paul
>>
>> --
>>
>> "Patrick Riley" <(E-Mail Removed)> wrote in message
>> news:C0FC2015-CD95-43EB-A2A4-(E-Mail Removed)...
>> > Thanks to some generous help from users of this site, I, with no Visual
>> > Basic
>> > knowledge, am using some VB code that requires the user to type his/her
>> > name
>> > into a given cell before the file can be saved, since the name must
>> > appear
>> > on
>> > the printed version. The user-name field is intentionally left blank.
>> > Upon
>> > attempting to save, the code presents a message to type in the name, if
>> > the
>> > name-field is still blank.
>> > Well, CATCH-22. This very code is preventing me from leaving the name
>> > field
>> > blank when I try to save the file in final form, ready for enduser use.
>> > The code I am using is:
>> >
>> > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
>> > Cancel As Boolean)
>> > If IsEmpty(Sheets("Main").Range("E59").Value) Then
>> > MsgBox "You must type in your name before " & _
>> > "this file can be saved.", 16, "ERROR"
>> > Cancel = True
>> > End If
>> > End Sub
>> >
>> > Any suggestions?
>> > ---Pat Riley
>>
>>
>>

Excellent suggestions for the OP (especially turning off events).

One additional one- extension of JLGWhiz's response; rather than always
deleting the contents of the target cell during the workbook open event,
check it for a specific string (such as "Patrick Riley" or "Erase Me") and
only clear that cell during the workbook open event if the target cell
contains your specified string.

Best,
Keith

"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Before you (as the developer, not the user) save the file, you can turn
> off
> events.
>
> Open the VBE
> hit ctrl-g to see the immediate window
> type this and hit enter:
> application.enableevents = false
>
> Then save your workbook--the _beforesave event won't fire.
>
> Then back to the VBE and toggle the setting in the immediate window:
> application.enableevents = true
>
> ====
> You could also do other stuff, too. Maybe check the username?
>
> if lcase(application.username) = lcase("Patrick Riley") then
> 'do nothing, let it save...
> 'or clear that cell!
> me.sheets("Main").range("e59").value = ""
> else
> If IsEmpty(me.Sheets("Main").Range("E59").Value) Then
> MsgBox "You must type in your name before " & _
> "this file can be saved.", 16, "ERROR"
> Cancel = True
> End If
> end if
>
>
>
> Patrick Riley wrote:
>>
>> Thanks to some generous help from users of this site, I, with no Visual
>> Basic
>> knowledge, am using some VB code that requires the user to type his/her
>> name
>> into a given cell before the file can be saved, since the name must
>> appear on
>> the printed version. The user-name field is intentionally left blank.
>> Upon
>> attempting to save, the code presents a message to type in the name, if
>> the
>> name-field is still blank.
>> Well, CATCH-22. This very code is preventing me from leaving the name
>> field
>> blank when I try to save the file in final form, ready for enduser use.
>> The code I am using is:
>>
>> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
>> Cancel As Boolean)
>> If IsEmpty(Sheets("Main").Range("E59").Value) Then
>> MsgBox "You must type in your name before " & _
>> "this file can be saved.", 16, "ERROR"
>> Cancel = True
>> End If
>> End Sub
>>
>> Any suggestions?
>> ---Pat Riley
>
> --
>
> Dave Peterson

Just to be sure, I re-copied the code from this post. I made sure that E59
was blank (I clicked in the cell and hit the Delete key). When I tried to
close the file, I got a syntax error.
In the VB editor, Lines 1 and 2 (beginning w "Private" and "Cancel") were
highlighted in yellow, and line 5 (beginning w "Range") was selected.
Incidentally, when I first pasted the code into the right-hand pane (after
selecting "ThisWorkbook" in left pane), lines 5 thru 7 appeared in red.
Since I am not a VB programmer, I don't know the significance, if any, of the
points I raise in this paragraph.
I don't know how to turn off events, although now a later response to my
post, from Dave Peterson, provides code to do so, coupled with a coding
suggestion.

I appreciate your taking the time to help!
---Pat

"PCLIVE" wrote:

> Just to be sure, you are using:
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
> Cancel As Boolean)
> If IsEmpty(Sheets("Main").Range("E59").Value) _
> Then
> Range("E59").Value = InputBox("You must type in your name before " &
> _
> "this file can be saved.", "Enter Name.")
> Else
> End If
> End Sub
>
> Are you sure E59 is currently empty. The code works when I attempt saving.
> But if there is a space or anything else in E59, no input box is displayed.
> Also, I saw in another post where you were to disable events. Make sure
> they are not currently disabled.
>
> Regards.
>
> --
>
> "Patrick Riley" <(E-Mail Removed)> wrote in message
> news:35CD6EB5-04EF-466A-BCB0-(E-Mail Removed)...
> > Paul:
> >
> > Thanks for taking the time to respond.
> > I deleted existing VB code, and used your code. After inserting your
> > code,
> > nothing happened:
> > I could save file with name-field blank. I did not see an input-box
> > appear.
> > I saved file and re-opened, and got the same results: no input box, could
> > still save file with name-field blank.
> >
> > "PCLIVE" wrote:
> >
> >> Why not just have an input box that will populate the cell?
> >>
> >> If IsEmpty(Sheets("Main").Range("E59").Value) _
> >> Then
> >> Range("E59").Value = InputBox("You must type in your name before
> >> " &
> >> _
> >> "this file can be saved.", "Enter Name.")
> >> Else
> >> End If
> >>
> >>
> >> HTH,
> >> Paul
> >>
> >> --
> >>
> >> "Patrick Riley" <(E-Mail Removed)> wrote in message
> >> news:C0FC2015-CD95-43EB-A2A4-(E-Mail Removed)...
> >> > Thanks to some generous help from users of this site, I, with no Visual
> >> > Basic
> >> > knowledge, am using some VB code that requires the user to type his/her
> >> > name
> >> > into a given cell before the file can be saved, since the name must
> >> > appear
> >> > on
> >> > the printed version. The user-name field is intentionally left blank.
> >> > Upon
> >> > attempting to save, the code presents a message to type in the name, if
> >> > the
> >> > name-field is still blank.
> >> > Well, CATCH-22. This very code is preventing me from leaving the name
> >> > field
> >> > blank when I try to save the file in final form, ready for enduser use.
> >> > The code I am using is:
> >> >
> >> > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
> >> > Cancel As Boolean)
> >> > If IsEmpty(Sheets("Main").Range("E59").Value) Then
> >> > MsgBox "You must type in your name before " & _
> >> > "this file can be saved.", 16, "ERROR"
> >> > Cancel = True
> >> > End If
> >> > End Sub
> >> >
> >> > Any suggestions?
> >> > ---Pat Riley
> >>
> >>
> >>
>
>
>

Dave:
I opened VBE, hit Ctrl-G, the Immediate Window opened, I cut and pasted the
"application.enableevents = false" code (without the quote marks) into
Immediate Window. When I tried to save the file, however, the beforesave
code fired, producing the warning message to type in the name. Incidentally,
the code I am using is:

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

Also, re your suggestion to check the username, under Tools>Options>General,
my username is pjriley, so I presumed I should use "pjriley" instead of
"Patrick Riley" in the code that you provided. I tried it, and IT LET ME
SAVE THE FILE with the BLANK name field!! BUT...when I e-mailed the file to
another user and tested it at his machine under his login and application
name (making sure the VB code was still there and unchanged), it did NOT
prompt me to fill in the name field. Under Tools>Options>General, his
username was "acutti", as it should have been.
What do you make of this?
---Pat

"Dave Peterson" wrote:

> Before you (as the developer, not the user) save the file, you can turn off
> events.
>
> Open the VBE
> hit ctrl-g to see the immediate window
> type this and hit enter:
> application.enableevents = false
>
> Then save your workbook--the _beforesave event won't fire.
>
> Then back to the VBE and toggle the setting in the immediate window:
> application.enableevents = true
>
> ====
> You could also do other stuff, too. Maybe check the username?
>
> if lcase(application.username) = lcase("Patrick Riley") then
> 'do nothing, let it save...
> 'or clear that cell!
> me.sheets("Main").range("e59").value = ""
> else
> If IsEmpty(me.Sheets("Main").Range("E59").Value) Then
> MsgBox "You must type in your name before " & _
> "this file can be saved.", 16, "ERROR"
> Cancel = True
> End If
> end if
>
>
>
> Patrick Riley wrote:
> >
> > Thanks to some generous help from users of this site, I, with no Visual Basic
> > knowledge, am using some VB code that requires the user to type his/her name
> > into a given cell before the file can be saved, since the name must appear on
> > the printed version. The user-name field is intentionally left blank. Upon
> > attempting to save, the code presents a message to type in the name, if the
> > name-field is still blank.
> > Well, CATCH-22. This very code is preventing me from leaving the name field
> > blank when I try to save the file in final form, ready for enduser use.
> > The code I am using is:
> >
> > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
> > Cancel As Boolean)
> > If IsEmpty(Sheets("Main").Range("E59").Value) Then
> > MsgBox "You must type in your name before " & _
> > "this file can be saved.", 16, "ERROR"
> > Cancel = True
> > End If
> > End Sub
> >
> > Any suggestions?
> > ---Pat Riley
>
> --
>
> Dave Peterson
>