Combo box creation / deletion

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Combo box creation / deletion

  1. #1
    New Member
    Join Date
    Jul 2002
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Jul 2002
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com