"Must" fill in cell

[Serenity99]

How do I make a field so that those completing it MUST fill it in?

 

[shades]

Use Validation:

Data > Validation. In the first tab choose "Custom" then in the Formul
type this:

=A1<>""

then uncheck "Ignore blank"

If you want a warning sign, then click on last tab "Error Alert" an
type in the title and message you want.

Click Okay.

 

[Serenity99]

*Use Validation:

Data > Validation. In the first tab choose "Custom" then in the
Formula type this:

=A1<>""

then uncheck "Ignore blank"

If you want a warning sign, then click on last tab "Error Alert" and
type in the title and message you want.

Click Okay. *

Thank you.

Can you tell me what the =A1<>"" means?

 

[Serenity99]

*Use Validation:

Data > Validation. In the first tab choose "Custom" then in the
Formula type this:

=A1<>""

then uncheck "Ignore blank"

If you want a warning sign, then click on last tab "Error Alert" and
type in the title and message you want.

Click Okay. *

Ooops! When I put in the formula as per the above, protected it, and
saved it and then reopened it to try it, it let me go right past the
field without putting anything in it. Of course, I don't have it set up
as a form, just a regular worksheet. Perhaps I need to set it up as a
form.

Do I have to recreate the whole thing if I want it as a form? I merely
created the expense report form as a worksheet and put formulas in.

 

[shades]

It means that if the value of cell A1 is "empty" (nothing entered) then
it will post the warning. If cell A1 has anything in it, number, word,
symbol, then no warning. It just insures that it will not be left
blank.

 

[shades]

Ooops! When I put in the formula as per the above, protected it, and
saved it and then reopened it to try it, it let me go right past the
field without putting anything in it. Of course, I don't have it set
up as a form, just a regular worksheet. Perhaps I need to set it up
as a form.

Do I have to recreate the whole thing if I want it as a form? I
merely created the expense report form as a worksheet and put
formulas in.

No, you don't need a form. If you are going to protect the sheet, but
still want cell A1 filled, then you have to right-click the cell, go to
Protection tab, and uncheck "locked." If not, then by being locked, it
will not even allow entry of any kind.

 

[Serenity99]

*No, you don't need a form. If you are going to protect the sheet,
but still want cell A1 filled, then you have to right-click the cell,
go to Protection tab, and uncheck "locked." If not, then by being
locked, it will not even allow entry of any kind. *

Now I am getting a message that says "a user has restricted the values
that can be entered in this cell" whenever I try to put anything in it.

 

[shades]

Okay. I am assuming that A1 is the cell you want something entered.

Go back to Data > Validation. Be sure that in the drop down box in the
first tab that you choose "Custom" then in the Formula Box below that
type this:

=A1<>""

Be sure to uncheck "Ignore blank"

I just went through the process (including unlocking the cell and
protecting the sheet and workbook) and it worked that whenever I put a
number, letter, etc. it was okay, but if I tried to clear it, then the
warning box came up.

 

[Gord Dibben]

Serenity

Only by using VBA and then only if user enables macros and if user doesn't
just turn computer off or some such action.

For code see this google search result.

http://snipurl.com/3q83

Gord Dibben Excel MVP

 

[Serenity99]

*Okay. I am assuming that A1 is the cell you want something entered.

Go back to Data > Validation. Be sure that in the drop down box i
the first tab that you choose "Custom" then in the Formula Box belo
that type this:

=A1<>"" (NOTE: make sure there is no space between the quote marks)

Be sure to uncheck "Ignore blank"

I just went through the process (including unlocking the cell an
protecting the sheet and workbook) and it worked that whenever I pu
a number, letter, etc. it was okay, but if I tried to clear it, the
the warning box came up. *

I am STILL getting an error. I can put data in the field but it if
try to erase it I get a message as soon as I enter another cell.
However, if I open the file and don't go anywhere near this field i
will still allow me to put data in other fields and save it WITHOU
prompting me to put data in this field.

My procedure:

Open File
Unprotect worksheet
Put cursor on this cell
Unlock cell
Go to Data/Validation and enter formula you gave me
Uncheck "ignore blanks"
Go to cell, format it (it is actually a date field)
Protect the worksheet
Save fil

 

[Serenity99]

when a user opens the form I am creating, how can I force them to go t
a specific field and enter data? (I don't want these fields to be lef
blank)

 

[Dave Peterson]

Maybe you could add some validation to the "OK" button on your form. If the
control isn't correct, give an error message and tell them to fix it.

Or maybe you could not enable the "ok" button until all the controls pass
validation.

And take a look at .setfocus (to select the control) in VBA's help.

 

[dcronje]

Try entering the validation formula in the cells that you don't wan
filled in unless the date cell has been filled in.

eg.

If you want to make sure A1 is filled in before the user can ente
other details in to cell B1. Copy the validation formula to cell B
only (You can do the same for a range)

Also customise the message in the error alert Tab to say something lik
"Please enter the date in A1 before continuing." This way when he trie
to enter data into B1 with A1 being empty it will give him the abov
message.

Hopefully this is what you are looking for