Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems with Validation object Add and Modify methods | Excel Programming | |||
Formula1 Property of Validation Object to refer to function name | Excel Programming | |||
TO MODIFY A EXCEL OBJECT FROM WORD USING VBA | Excel Programming | |||
VBA: using ignoreblank property | Excel Programming | |||
Loop thru multiple files - Modify worksheet visible property | Excel Programming |