Names of Drop_down Boxes

[Darin Kramer]

Hi guys,
By clicking a Macro button 1, a user launches a macro
One element of the macro is copying a drop down box from sheet A to
sheet B.

By click Marcro Button 2, the macro that is launched needs to select
that copied drop down box on sheet B and delete it.

Drop down box is always visible on sheet A.

The problem is whenever you copy and paste a drop down box it RENAMES
the drop down box with the next consecutive number, and then I cant
write a macro to SELECT it to delete it, cause I dont know what the
number will be.

Solns...? perhaps VB to name the checkbox once it is pasted the first
time...?

Thanks

D

 

[Tom Ogilvy]

Assuming a dropdown box from the forms toolbar.

worksheets("Sheet2").Dropdowns.Delete

if it is the only one.

If not and you know what cell it is located above

for each drpdwn in worksheets("Sheet2").Dropdowns
if drpdwn.TopleftCell.Address = "$B$9" then
drpdwn.Delete
exit for
end if
Next

 

[Darin Kramer]

You are right, there is more than one drop down box
It scrolls throught the formulae but never actually deletes the box,
(skips over after the then) ie think the cell reference is invalid.
The drop down is placed on e64, but stretches into f64, I have tried
references e63, and f63 (and e and f 65, but too no avail...)

 

[Tom Ogilvy]

Sub Tester1()
Dim drpdwn As DropDown
Dim rng As Range
For Each drpdwn In Worksheets("Sheet2").DropDowns
Set rng = drpdwn.TopLeftCell
drpdwn.Select
MsgBox drpdwn.Name & ": " & rng.Address(0, 0)
Set rng = rng.Offset(-1, -1).Resize(3, 3)
If Not Intersect(rng, Range("E64")) Is Nothing Then
drpdwn.Delete
Exit For
End If
Next

End Sub

worked for me. Obviously after you get it working you will want to take out
some of the feedback stuff.

 

[Darin Kramer]

It works !! You are a genius. Thanks so much!