Data validation not possible

[Hari]

Hi,

My colleague was working on an Unprotected excel document and he wanted
to apply a validation list (list is in A1 through A5) in region C2
through C10. We had selected C1 through C10 and went to
Data--Validation-LIst then selected A1 through A5 and pressed OK. Now,
when the active cell is any of the cells through C2 through C10, then
we dont get down arrow associated with valdation list? Rather the cell
has no validation applied when we reselect these cells and go to data
-- validation. Note we are able to apply validation on other workbooks.
(This happened in Office 2003 and we havent tried this whether it
happens in 2002 or not)

Why is it happening?

Regards,
Hari
India

 

[Ron de Bruin]

Hi Hari

I see this more and the only way to reproduce it for me is to delete shapes
on that sheets with a macro like this :

Warning :
Not use code like below because Excel crash if there are comments on the sheet and
It delete the AutoFilter dropdowns and the dropdowns of the cells with
Data>Validation (List option) if you have them on your worksheet.

Sub NotUseThisMacro()
'Loop through the Shapes collection
Dim myshape As Shape
For Each myshape In ActiveSheet.Shapes
myshape.Delete
Next myshape
End Sub

See other code here
http://www.rondebruin.nl/controlsobjectsworksheet.htm


You can insert a new worksheet in the workbook
Select all cells on the problem sheet (ctrl A, 2 times)
Then Copy (Ctrl C)
Go to the new worksheet in A1 and paste (ctrl V)

 

[Ron de Bruin]

You can also look here to be sure you not hide them

Tools>Options>View...Objects

 

[Hari]

Ron,

My colleague finally tried your solution yesterday and it has WORKED!!.

Thanks a lot for your help.

(I might have some related doubts on this and would get back later)

Regards,
Hari
India

 

[Ankit]

Ron,

I tried your solution and realised that the objects were hidden. So I
used ' Tools>Options>Objects>ShowAll ' to unhide them and this solution
worked.
However I have a doubt regarding your first solution. You mentioned
that validation doesn't work if the sheet has some other objects. I put
some objects from my side in that sheet and applied some validation.
Also I put the comment on the same cell.But the validation worked.

So I think the perfect solution to this problem was the one you
mentioned obove (unhide the objects).
Thanks for all your help.

Ankit
India

 

[Ron de Bruin]

Hi Ankit

However I have a doubt regarding your first solution. You mentioned
that validation doesn't work if the sheet has some other objects.

No

If you use the delete code that I posted it will delete the validation dropdowns in the sheet.
Then you can't use the dropdowns on that sheet anymore.