Combo box creation / deletion

wigwam

New Member
Joined
Jul 31, 2002
Messages
42
Guys

Have used the code below to create two combo boxes on my sheet. However ... I cannot find a way to delete these prior to recreating ... data changes.

Can someone let me know how ... because of recreate I do not know there names.

thanks

Sub CreateDropDowns()
ActiveSheet.DropDowns.Add(1200, 20, 250, 22).Select

With Selection
.ListFillRange = "'Temp - Index'!$K$1:$K$8"
.LinkedCell = "'Temp - Index'!$K$12"
.DropDownLines = 8
.Display3DShading = True
.Caption = "Company Name : "
End With

ActiveSheet.DropDowns.Add(1200, 50, 250, 22).Select

With Selection
.ListFillRange = "'Temp - Index'!$L$1:$L$" & _
Worksheets("Temp - Index").Range("M1")
.LinkedCell = "'Temp - Index'!$K$14"
.DropDownLines = 8
.Display3DShading = True
.Caption = "Finance Type : "
.OnAction = "SelectionTaken"
End With

End Sub
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
You can use Object variables to keep track of your DropDowns, like this:

Code:
Dim Drp1 As Object
Dim Drp2 As Object

Sub CreateDropDowns()
    Set Drp1 = ActiveSheet.DropDowns.Add(1200, 20, 250, 22)
    With Drp1
        .ListFillRange = "'Temp - Index'!$K:$K"
        .LinkedCell = "'Temp - Index'!$K"
        .DropDownLines = 8
        .Display3DShading = True
        .Caption = "Company Name : "
    End With
    Set Drp2 = ActiveSheet.DropDowns.Add(1200, 50, 250, 22)
    With Drp2
        .ListFillRange = "'Temp - Index'!$L:$L$" & _
        Worksheets("Temp - Index").Range("M1")
        .LinkedCell = "'Temp - Index'!$K"
        .DropDownLines = 8
        .Display3DShading = True
        .Caption = "Finance Type : "
        .OnAction = "SelectionTaken"
    End With
End Sub

Sub RemoveDropDowns()
    Drp1.Delete
    Drp2.Delete
End Sub
 
Upvote 0
Andrew .. thanks for that ... does make it better !!!

If I need to search for the combo's do you know a good way to look for them

cheers
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top