Names of Drop_down Boxes

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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


--
Regards,
Tom Ogilvy

"Darin Kramer" <[email protected]> wrote in message
news:[email protected]...
>
>
> 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
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

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...)



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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.

--
Regards,
Tom Ogilvy

"Darin Kramer" <[email protected]> wrote in message
news:[email protected]...
> 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...)
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

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




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!