Making a cell mandatory if another cell has a particular value

[lisay]

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]

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]

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!

 

[Gord Dibben]

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 as>file type>template(*.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 File>New...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

 

[lisay]

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

 

[Dave Peterson]

I'd qualify that C6 range, too:

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

 

[Dave Peterson]

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.

 

[Gord Dibben]

I'll try to remember that Dave


Gord

I'd qualify that C6 range, too:

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

 

[lisay]

Thank you, both! Got it to work!

I'll try to remember that Dave


Gord



.