Modify the IgnoreBlank property of the Validation object
 

It seems that to change the IgnoreBlank property of the Validation object for
a cell, you need to delete and recreate the whole Validation object.

Is that true?

I want to make the user select something from the dropdown sometimes, but
other times he can either select something or enter his own information.

Thanks in advance.

Modify the IgnoreBlank property of the Validation object
 

you need to delete and recreate the whole Validation object. Is that
true?

I don't think so.

I want to make the user select something from the dropdown sometimes, but
other times he can either select something or enter his own information.

With a list validation a user entry has to match the list all the time.
There is no option to have it turn itself off say during tea time. You
might be able to get the effect you want using the "custom" type of data
validation. For example:

=OR(EXACT(B1,"off"),NOT(ISERROR(MATCH(D1,A1:A3,0)) ))

This custom validation formula for cell D1 should block any entry that
doesn't match the A1:A3 list unless B1 has "off" in it. (I didn't test this
extensively so it may have to be fine-tuned. I think a blank B1 would allow
any entry too so B1 shouldn't be blank).

One thing about the misleading "ignore blank" option. It does not keep
someone from leaving the cell empty or clearing it. It just blocks (when
it's unchecked) a "blank" entry, like pressing F2, Enter with the cell
empty.

--
Jim
"susiew32" wrote in message
...
| It seems that to change the IgnoreBlank property of the Validation object
for
| a cell, you need to delete and recreate the whole Validation object.
|
| Is that true?
|
| I want to make the user select something from the dropdown sometimes, but
| other times he can either select something or enter his own information.
|
| Thanks in advance.

Modify the IgnoreBlank property of the Validation object
 

To create a dropdown list, and allow users to enter other values, go to
the Error Alert tab in the Data Validation dialog box.

Remove the check mark from 'Show error alert after invalid data is entered'
OR
From the Style dropdown, choose Warning or Information, instead of Stop.

susiew32 wrote:
It seems that to change the IgnoreBlank property of the Validation object for
a cell, you need to delete and recreate the whole Validation object.

Is that true?

I want to make the user select something from the dropdown sometimes, but
other times he can either select something or enter his own information.

Thanks in advance.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Modify the IgnoreBlank property of the Validation object
 

I think the problem with this approach may be that she wants (as I read it)
to "sometimes" require a match and sometimes not. Your suggestion is all or
none. Now I don't know how we determine when it's one way or the other but
my approach will key off a cell entry/formula that could be a way.

--
Jim
"Debra Dalgleish" wrote in message
...
| To create a dropdown list, and allow users to enter other values, go to
| the Error Alert tab in the Data Validation dialog box.
|
| Remove the check mark from 'Show error alert after invalid data is
entered'
| OR
| From the Style dropdown, choose Warning or Information, instead of Stop.
|
| susiew32 wrote:
| It seems that to change the IgnoreBlank property of the Validation
object for
| a cell, you need to delete and recreate the whole Validation object.
|
| Is that true?
|
| I want to make the user select something from the dropdown sometimes,
but
| other times he can either select something or enter his own information.
|
| Thanks in advance.
|
|
| --
| Debra Dalgleish
| Contextures
| http://www.contextures.com/tiptech.html
|

Modify the IgnoreBlank property of the Validation object
 

Here's what I want. If the user puts an X in a cell (or anything in the
cell), that means what they want to enter will not be in the drop down and
they will enter their own information. If they leave the cell blank, then
they will select from the dropdown information pulled from a list on a
different sheet.

The validations in the cells with the drop downs are set up. If the user
puts an X in a cell, then in the change event, I just delete the validation
altogether. If they clear the cell, I recreate the validation.

I was hoping I could just toggle the IgnoreBlank property, which, unlike its
name, does seem to allow the user to enter anything he wants in the cell.

So it's done, but not as elegantly as I wanted it to be.

"Jim Rech" wrote:

I think the problem with this approach may be that she wants (as I read it)
to "sometimes" require a match and sometimes not. Your suggestion is all or
none. Now I don't know how we determine when it's one way or the other but
my approach will key off a cell entry/formula that could be a way.

--
Jim
"Debra Dalgleish" wrote in message
...
| To create a dropdown list, and allow users to enter other values, go to
| the Error Alert tab in the Data Validation dialog box.
|
| Remove the check mark from 'Show error alert after invalid data is
entered'
| OR
| From the Style dropdown, choose Warning or Information, instead of Stop.
|
| susiew32 wrote:
| It seems that to change the IgnoreBlank property of the Validation
object for
| a cell, you need to delete and recreate the whole Validation object.
|
| Is that true?
|
| I want to make the user select something from the dropdown sometimes,
but
| other times he can either select something or enter his own information.
|
| Thanks in advance.
|
|
| --
| Debra Dalgleish
| Contextures
| http://www.contextures.com/tiptech.html
|