Making a cell mandatory if another cell has a particular value
 

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

Making a cell mandatory if another cell has a particular value
 

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

Making a cell mandatory if another cell has a particular value
 

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
.

Making a cell mandatory if another cell has a particular value
 

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
.

Making a cell mandatory if another cell has a particular value
 

Thank you, Gord! The code works perfectly!

I am sorry but I am not sure I understand which window when you refer to
"Immediate Window". Could you help clarify please?

Thanks again for the help.
Lisa


"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
.


.

Making a cell mandatory if another cell has a particular value
 

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

Making a cell mandatory if another cell has a particular value
 

If you as the developer need to save the file in an "invalid" state, you can go
into the VBE and disable events--so that this check doesn't stop you from saving
the file.

You can use that single line of code that Gord suggested in the immediate window
of the VBE.

Open the VBE (alt-f11 is nice).
then hit ctrl-g
and you'll see that immediate window.



lisay wrote:

Thank you, Gord! The code works perfectly!

I am sorry but I am not sure I understand which window when you refer to
"Immediate Window". Could you help clarify please?

Thanks again for the help.
Lisa

"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

Making a cell mandatory if another cell has a particular value
 

I'll try to remember that Dave


Gord

On Sat, 20 Mar 2010 06:45:28 -0500, Dave Peterson
wrote:

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
.

Making a cell mandatory if another cell has a particular value
 

Thank you, both! Got it to work!

"Gord Dibben" wrote:

I'll try to remember that Dave


Gord

On Sat, 20 Mar 2010 06:45:28 -0500, Dave Peterson
wrote:

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
.


.