How to copy Dropdown boxes?

[salut]

I have several Dropdown boxes created from Toolbars->Forms. And I am trying
to copy them into another sheet of the same workbook. The strange thing is
when I do it manually, it is totally fine. No problem at all.

When I try to record a macro, This is what I get
------------------------------------------------------------------------------------------------
Range("A10:B26").Select
Selection.Copy
Sheets("Destination").Select
Range("A11").Select
ActiveSheet.DropDowns.Add(5.25, 222, 87, 21.75).Select
ActiveSheet.DropDowns.Add(5.25, 298.5, 87, 21.75).Select
ActiveSheet.DropDowns.Add(3, 369, 89.25, 23.25).Select
ActiveSheet.Paste
------------------------------------------------------------------------------------------------
The problem is when I was trying to run the macro I just recorded, the macro
didn't do the exact samething as I did manually when I was recording the
macro. It will put some Dropbox in the same place. But my format control is
all gone. And the macro will put another picture of the 3 dropdown box
somewhere on the spreadsheet. And the text around the dropdown boxes only
appears on the picture I get.

I was trying to investigate what's going on. The problem is I can't find the
object 'DropDowns' through the object explorer, and I can't find any 'Add'
Method like what is recorded in the macro. Could anybody help me out? Thanks
a lot!

[Leith Ross]

Hello Salut,

The Forms controls are belong to the Shapes class of the Worksheet. Here is a macro that will copy all the Drop Downs from one Worksheet to another.

Please Login or Register  to view this content.

Sincerely,
Leith Ross