data validation list source (Excel2000)

Topic

New Reply

I am using data validation with a ‘List’ source.
I get different results depending on whether I use a named range (on another sheet), or when I directly specify the choices (separating each by commas):
When the cell containing the data validation is ‘locked’ with sheet protection on,
..if I use the comma separated version, I get the expected ‘cell is locked’ Excel message when selecting from the dropdown
..if I use the named range as a source, a selection can be made from the dropdown even if the cell is locked and protected.

I want to use named ranges as my source (easier maintenance) but I want the Excel ‘cell is protected’ message if I want to disallow selection.

Any suggestions?

zeddy

Reply | Quote

Replies

I can replicate the problem in XL97 and it sounds like a “bug” to me.

A “relatively” easy “workaround” is:
when you “lock” the input cells to prevent data entry, rename the named ranges.

If there are a lot of them, you could create them using a formula rather than a range (eg with OFFSET) and then have the an added variable that sets the rows to zero. with no rows, there is no list.
Something like this: (change the names as appropriate)
=OFFSET(ListSheet!$A$1,0,0,COUNTA(ListSheet!$A:$A)*ListSheet!$B$1,1)
List starts in A1 of ListSheet and will expand and contract (automatcially) based on the entries in column A. ListSheet!B1 contains TRUE/FALSE: if true the list will be as long as the number of items in col A, if false it will be zero and the list will not work.

B1 could be the linked cell for a checkbox if desired.

Warning: if the sheet is not protected or the cell is not locked, and you enter false in B1 then the “validation” does not come into play and any value may be entered in the cell (manually, nothing can be entered using the dropdown)

Steve

Reply | Quote

For info the bug has been fixed in XL 2003 and you get the error message for comma separated lists and named ranges.

Reply | Quote

Hi Tony

..so when my Users finally upgrade in 2008 it will be fixed!

..I guess one solution would be to ‘delete’ the validation when the sheet is ‘protected’, and to use event trapping to ‘re-introduce’ the required data validation when the sheet is
‘unlocked’.

Trouble is I need to do this on a ‘per record’ i.e. row basis.
I use event trapping to set certain allowable conditions.
For example, I have two sections of columns for data entry.
You can’t input data into section 2 columns on a record row unless section 1 columnson the row are completed first.
Section 2 columns contain cells with data validation dropdown lists.
These are the ones I want to give a ‘cell protected message’ if the cells are ‘locked’.

zeddy

Reply | Quote

Instead of using data validation, have you thought of using actual comboboxes?

The ones from the control toolbox have much more control and you can enable and disable them easily using code.

Steve

Reply | Quote

Hi Steve,

..I shall look at comboboxes as suggested.
Hmmm.The beauty of Excel is there are so many ways to skin the cat.
(apologies to any pet-loving non Brits – re ‘there are so many way to skin a cat’)

zeddy

Reply | Quote

Out of curiosity (which killed the cat, by the way), why is it that one would go around skinning cats in the first place, much less need more than one way of doing so? I’ve never understood that expression.

Reply | Quote

I always heard that is was a southern expression and more completely is:
“more ways to skin a catfish”
but was abbreviated since catfish are called cat.

Steve

Reply | Quote

I always heard that is was a southern expression and more completely is:
“more ways to skin a catfish”
but was abbreviated since catfish are called cat.

Steve

Reply | Quote

MORE THAN ONE WAY TO SKIN A CAT
and other Catch Phrases

Alan

Reply | Quote

MORE THAN ONE WAY TO SKIN A CAT
and other Catch Phrases

Alan

Reply | Quote

Out of curiosity (which killed the cat, by the way), why is it that one would go around skinning cats in the first place, much less need more than one way of doing so? I’ve never understood that expression.

Reply | Quote

Hi Steve,

..I shall look at comboboxes as suggested.
Hmmm.The beauty of Excel is there are so many ways to skin the cat.
(apologies to any pet-loving non Brits – re ‘there are so many way to skin a cat’)

zeddy

Reply | Quote

Instead of using data validation, have you thought of using actual comboboxes?

The ones from the control toolbox have much more control and you can enable and disable them easily using code.

Steve

Reply | Quote

Hi Tony

..so when my Users finally upgrade in 2008 it will be fixed!

..I guess one solution would be to ‘delete’ the validation when the sheet is ‘protected’, and to use event trapping to ‘re-introduce’ the required data validation when the sheet is
‘unlocked’.

Trouble is I need to do this on a ‘per record’ i.e. row basis.
I use event trapping to set certain allowable conditions.
For example, I have two sections of columns for data entry.
You can’t input data into section 2 columns on a record row unless section 1 columnson the row are completed first.
Section 2 columns contain cells with data validation dropdown lists.
These are the ones I want to give a ‘cell protected message’ if the cells are ‘locked’.

zeddy

Reply | Quote

For info the bug has been fixed in XL 2003 and you get the error message for comma separated lists and named ranges.

Reply | Quote

Hi Steve,

I like the idea but I need to allow the dropdown entries in certain data record rows but not in others.
I have used locked cells to distinguish which.

zeddy

Reply | Quote

You could use something like this:
=OFFSET(ListSheet!$A$1,0,0,COUNTA(ListSheet!$A:$A)*(ROW()=ListSheet!$B$1),1)

If you put 5 in ListSheet!B1 (eg) the named range will only have a size if the active row = 5. In all other rows the pulldown will be disabled. You could use an OR if you want multiple rows enabled. Perhaps even a MATCH function in a “list of rows”

Perhaps even another named range that just evaluates whether the active row is “enabled”.

Plenty of possibilities to test if desired.

The warning still goes though, disable the list and any value may be eneterd in the validation cell, so it must be protected.

Steve

Reply | Quote

You could use something like this:
=OFFSET(ListSheet!$A$1,0,0,COUNTA(ListSheet!$A:$A)*(ROW()=ListSheet!$B$1),1)

If you put 5 in ListSheet!B1 (eg) the named range will only have a size if the active row = 5. In all other rows the pulldown will be disabled. You could use an OR if you want multiple rows enabled. Perhaps even a MATCH function in a “list of rows”

Perhaps even another named range that just evaluates whether the active row is “enabled”.

Plenty of possibilities to test if desired.

The warning still goes though, disable the list and any value may be eneterd in the validation cell, so it must be protected.

Steve

Reply | Quote

Hi Steve,

I like the idea but I need to allow the dropdown entries in certain data record rows but not in others.
I have used locked cells to distinguish which.

zeddy

Reply | Quote

I can replicate the problem in XL97 and it sounds like a “bug” to me.

A “relatively” easy “workaround” is:
when you “lock” the input cells to prevent data entry, rename the named ranges.

If there are a lot of them, you could create them using a formula rather than a range (eg with OFFSET) and then have the an added variable that sets the rows to zero. with no rows, there is no list.
Something like this: (change the names as appropriate)
=OFFSET(ListSheet!$A$1,0,0,COUNTA(ListSheet!$A:$A)*ListSheet!$B$1,1)
List starts in A1 of ListSheet and will expand and contract (automatcially) based on the entries in column A. ListSheet!B1 contains TRUE/FALSE: if true the list will be as long as the number of items in col A, if false it will be zero and the list will not work.

B1 could be the linked cell for a checkbox if desired.

Warning: if the sheet is not protected or the cell is not locked, and you enter false in B1 then the “validation” does not come into play and any value may be entered in the cell (manually, nothing can be entered using the dropdown)

Steve

Reply | Quote