software

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
IndiaHi 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
Datagt;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
www.rondebruin.nl/controlsobjectsworksheet.htmYou 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)
--
Regards Ron de Bruin
www.rondebruin.nlquot;Hariquot; gt; wrote in message oups.com...
gt; Hi,
gt;
gt; My colleague was working on an Unprotected excel document and he wanted
gt; to apply a validation list (list is in A1 through A5) in region C2
gt; through C10. We had selected C1 through C10 and went to
gt; Data--Validation-LIst then selected A1 through A5 and pressed OK. Now,
gt; when the active cell is any of the cells through C2 through C10, then
gt; we dont get down arrow associated with valdation list? Rather the cell
gt; has no validation applied when we reselect these cells and go to data
gt; -- validation. Note we are able to apply validation on other workbooks.
gt; (This happened in Office 2003 and we havent tried this whether it
gt; happens in 2002 or not)
gt;
gt; Why is it happening?
gt;
gt; Regards,
gt; Hari
gt; India
gt;
You can also look here to be sure you not hide them

Toolsgt;Optionsgt;View...Objects

--
Regards Ron de Bruin
www.rondebruin.nlquot;Ron de Bruinquot; gt; wrote in message ...
gt; Hi Hari
gt;
gt; I see this more and the only way to reproduce it for me is to delete shapes
gt; on that sheets with a macro like this :
gt;
gt; Warning :
gt; Not use code like below because Excel crash if there are comments on the sheet and
gt; It delete the AutoFilter dropdowns and the dropdowns of the cells with
gt; Datagt;Validation (List option) if you have them on your worksheet.
gt;
gt; Sub NotUseThisMacro()
gt; 'Loop through the Shapes collection
gt; Dim myshape As Shape
gt; For Each myshape In ActiveSheet.Shapes
gt; myshape.Delete
gt; Next myshape
gt; End Sub
gt;
gt; See other code here
gt; www.rondebruin.nl/controlsobjectsworksheet.htm
gt;
gt;
gt; You can insert a new worksheet in the workbook
gt; Select all cells on the problem sheet (ctrl A, 2 times)
gt; Then Copy (Ctrl C)
gt; Go to the new worksheet in A1 and paste (ctrl V)
gt;
gt;
gt;
gt; --
gt; Regards Ron de Bruin
gt; www.rondebruin.nl
gt;
gt;
gt; quot;Hariquot; gt; wrote in message oups.com...
gt;gt; Hi,
gt;gt;
gt;gt; My colleague was working on an Unprotected excel document and he wanted
gt;gt; to apply a validation list (list is in A1 through A5) in region C2
gt;gt; through C10. We had selected C1 through C10 and went to
gt;gt; Data--Validation-LIst then selected A1 through A5 and pressed OK. Now,
gt;gt; when the active cell is any of the cells through C2 through C10, then
gt;gt; we dont get down arrow associated with valdation list? Rather the cell
gt;gt; has no validation applied when we reselect these cells and go to data
gt;gt; -- validation. Note we are able to apply validation on other workbooks.
gt;gt; (This happened in Office 2003 and we havent tried this whether it
gt;gt; happens in 2002 or not)
gt;gt;
gt;gt; Why is it happening?
gt;gt;
gt;gt; Regards,
gt;gt; Hari
gt;gt; India
gt;gt;
gt;
gt;
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
IndiaRon,

I tried your solution and realised that the objects were hidden. So I
used ' Toolsgt;Optionsgt;Objectsgt;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
IndiaHi Ankit

gt; However I have a doubt regarding your first solution. You mentioned
gt; 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.

--
Regards Ron de Bruin
www.rondebruin.nlquot;Ankitquot; gt; wrote in message ups.com...
gt; Ron,
gt;
gt; I tried your solution and realised that the objects were hidden. So I
gt; used ' Toolsgt;Optionsgt;Objectsgt;ShowAll ' to unhide them and this solution
gt; worked.
gt; However I have a doubt regarding your first solution. You mentioned
gt; that validation doesn't work if the sheet has some other objects. I put
gt; some objects from my side in that sheet and applied some validation.
gt; Also I put the comment on the same cell.But the validation worked.
gt;
gt; So I think the perfect solution to this problem was the one you
gt; mentioned obove (unhide the objects).
gt; Thanks for all your help.
gt;
gt; Ankit
gt; India
gt;