How to copy Dropdown boxes?

[Guest]

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 t
another.


Code
-------------------

Sub CopyDropDowns(ByVal From_Worksheet As String, ByVal To_Worksheet As String)

Set SrcWks = ActiveWorkbook.Worksheets(From_Worksheet)
Set DstWks = ActiveWorkbook.Worksheets(To_Worksheet)

For Each S In SrcWks.Shapes
If S.Type = msoFormControl Then
If S.FormControlType = xlDropDown Then
DstWks.Shapes.AddFormControl xlDropDown, S.Left, S.Top, S.Width, S.Height
End If
End If
Next S

End Sub