Can't create a drop down
 

Hi Brisbane Rob

You are number ?
Have you delete shapes with code on that sheet ?

You can add a new sheet and copy all cells from your worksheet (ctrl-a and then ctrl c)
and past in A1 of the new sheet


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Brisbane Rob" wrote in message
news:Brisbane.Rob.203moy_1134679501.0157@excelforu m-nospam.com...

I have a workbook with twenty sheets. I have drop down menus on most of
them and can create more, except on one sheet where the drop down won't
function. On checking the cell it shows that the cell has a data
validation setting and that the list is correctly identified. But no
arrow or drop down shows.

I assume there is some setting somewhere preventing it but I can't find
anything different about this sheet. I've tried copying and pasting from
another sheet but no difference.

Any ideas? It's driving me crazy!

Thanks


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=493915

Can't create a drop down
 

I have a workbook with twenty sheets. I have drop down menus on most of
them and can create more, except on one sheet where the drop down won't
function. On checking the cell it shows that the cell has a data
validation setting and that the list is correctly identified. But no
arrow or drop down shows.

I assume there is some setting somewhere preventing it but I can't find
anything different about this sheet. I've tried copying and pasting from
another sheet but no difference.

Any ideas? It's driving me crazy!

Thanks


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=493915

Can't create a drop down
 

Thanks.

What's this about deleting shapes with codes (which I did do on the
sheet)?


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=493915

Can't create a drop down
 

Hi Bob

If you loop through the shapes collection and delete the shapes it also delete
AutoFilter dropdowns and Data Validation

This is working if you want to delete all shapes

Sub Shapes1()
'Delete all Objects except Comments
ActiveSheet.DrawingObjects.Visible = True
ActiveSheet.DrawingObjects.Delete
End Sub

But if you do this not

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

The problem is Type 8 (Forms controls)
I have two workerounds if you only want to delete Forms controls on this page
http://www.rondebruin.nl/controlsobjectsworksheet.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Brisbane Rob" wrote in message
news:Brisbane.Rob.204iva_1134721201.2769@excelforu m-nospam.com...

Thanks.

What's this about deleting shapes with codes (which I did do on the
sheet)?


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=493915

Can't create a drop down
 

Create a new worksheet
Add two cells with Data|validation (with a list)

Then step through this code:

Option Explicit
Sub testme()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
shp.Delete
Next shp
End Sub

You'll break the data validation dropdown for those cells.

Ron has some notes:
http://www.rondebruin.nl/controlsobjectsworksheet.htm
Look for: Only delete the controls from the Forms toolbar

That show you ways to be more careful.



Brisbane Rob wrote:

Thanks.

What's this about deleting shapes with codes (which I did do on the
sheet)?

--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=493915

--

Dave Peterson