I'd qualify that C6 range, too:

If worksheets("Sheet1").Range("C6") = "" Then



Gord Dibben wrote:

You mention "Template" then say you want C6 cleared for the next user.

If saved as a true Template there is no need to have code to clear contents
of C6 because users never overwrite a Template, only fill in and save a copy
created from the Template.

To save as a true Template, save asfile typetemplate(*.xlt or *.xltm)

Place this code in Thisworkbook module before saving as a Template.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As _
Boolean, Cancel As Boolean)
With Sheets("Sheet1").Range("C4")
If .Value = "yy" Or .Value = "xx" Then
If Range("C6") = "" Then
MsgBox "Save has been cancelled. You must fill C6"
Cancel = True
End If
End If
End With
End Sub

In order to save as a Template with cells un-filled you must disable events
so's the code does not run and cancel saving.

In the immediate window enter this line

Application.EnableEvents = False

Now save as a Template.

In immediate window enter this line.

Application.EnableEvents = True

Close the Template.

Now go to FileNew...Templates on My Computer and double-click on your
Template.

This opens a copy for you to fill in.

If C4 "yy" or "xx" and C6 is blank, message will pop up and save is
cancelled.

Gord Dibben MS Excel MVP

On Fri, 19 Mar 2010 15:36:06 -0700, lisay
wrote:

Thank you!

Is there a way for me to force user entry before saving/exiting? This works
as an alert only but it still allows users to save the form without the field
being filled out.

Thanks!

"Dave Peterson" wrote:

I would use an adjacent cell and a formula:

=if(and(or(c4="xx",c4="yy"),c6=""),"Please answer C6","")

And format this cell in big, bold, red, letters.

If you have lots of values to check in C4, you could use an equivalent
variation:

=if(and(or(c4={"xx","yy"}),c6=""),"Please answer C6","")
(It's less typing)


lisay wrote:

Hi, any help would be appreciated!

I am trying to create a template where people fill out information about a
user. I want cell C6 to be filled out (NOT empty) if cell C4 = "XX" or C4 =
"YY".

Then, I want to be able to save the template where C6 is blank so that
people can fill it out later;

Thank you!
Lisa

--

Dave Peterson
.


--

Dave Peterson