Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Names of Drop_down Boxes
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" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Names of Drop_down Boxes
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Names of Drop_down Boxes
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" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Names of Drop_down Boxes
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data validation boxes, outputting a final number from the boxes | Excel Discussion (Misc queries) | |||
Moving. Want to number boxes and inventory contents of boxes | Charts and Charting in Excel | |||
Selecting subsets using combo boxes or list boxes | Excel Discussion (Misc queries) | |||
Questions on combo boxes and list boxes. | New Users to Excel | |||
How do i create a value for check boxes or option boxes | Excel Discussion (Misc queries) |