Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo boxed used to fill in list (2007)

    Ok I have a file set up to help me ask this question. On a worksheet I have 2 blank groups, a combo box, A command button, and a list of items

    What I want to do is bring up the list of items in the combo box, press the button and have the info shown in the combo box placed into the first open spot in the 1st group. pick something else and it goes into the next available spot in group 1. When group 1 is filled it continues onto group 2 doing the same thing.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo boxed used to fill in list (2007)

    Specify a LinkedCell in the properties of the combo box. I used C2 because it is neatly hidden behind the combo box.
    While in design view, double click the command button and write code for the Click event:
    <code>
    Private Sub CommandButton1_Click()
    Dim r As Long
    If Range("C2") = "" Then
    MsgBox "Please select an item in the combo box.", vbInformation
    Exit Sub
    End If
    r = Range("A21").End(xlUp).Row
    If r = 20 Then
    r = Range("A39").End(xlUp).Row
    If r = 38 Then
    MsgBox "Sorry, both lists are full.", vbExclamation
    Exit Sub
    End If
    End If
    Range("A" & (r + 1)) = Range("C2")
    End Sub
    </code>
    Turn off design mode and test.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo boxed used to fill in list (2007)

    If you'd rather not use a LinkedCell for the combo box, you can use its value directly:
    <code>
    Private Sub CommandButton1_Click()
    Dim r As Long
    If Me.ComboBox1 = "" Then
    MsgBox "Please select an item in the combo box.", vbInformation
    Exit Sub
    End If
    r = Range("A21").End(xlUp).Row
    If r = 20 Then
    r = Range("A39").End(xlUp).Row
    If r = 38 Then
    MsgBox "Sorry, both lists are full.", vbExclamation
    Exit Sub
    End If
    End If
    Range("A" & (r + 1)) = Me.ComboBox1
    End Sub</code>

  4. #4
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo boxed used to fill in list (2007)

    This worked great. now I need to have the combo box cleared as the info for the box is coming from a changing list so is their any way of refreshing the info or emptying the info shown in the box

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo boxed used to fill in list (2007)

    The list dispalyed by the combo box is determined by the ListFillRange property. If the data in the range referred to change, the list in the combo box will change.
    You can also change the ListFillRange property using code.

    And if you want to clear the text box part of the combo box, you can add a line

    Me.ComboBox1.Clear

    immediately above the End Sub.

  6. #6
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo boxed used to fill in list (2007)

    I tried the clearing option and got a run time error..

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo boxed used to fill in list (2007)

    OK, try
    <code>
    Me.ComboBox1 = ""</code>

  8. #8
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo boxed used to fill in list (2007)

    works great like always... thanks a lot

Posting Permissions

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