Combo boxed used to fill in list (2007)

Topic

New Reply

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.

Reply | Quote

Replies

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:

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

Turn off design mode and test.

Reply | Quote

If you’d rather not use a LinkedCell for the combo box, you can use its value directly:

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

OK, try

Me.ComboBox1 = ""

Reply | Quote

works great like always… thanks a lot

Reply | Quote