combobox question

[Giselle]

I'm new to comboboxes and need a few clues.

I have Combobox1 that lists 3 dates. Combobox2 lists the names of
volunteers.

I want to be able to make many selections from combobox2 to produce a LIST
of volunteers (not just change the value of 1 cell). If the 1st, 2nd or 3rd
date is selected, the list of volunteers should be in columns start ing in
C5, E5 or G5 respectively.

Could one of you gurus point me in the right direction?

Thanks in advance
Giselle

 

[Guest]

Giselle,
You need a ListBox to do multiselection. The following code is
a starter for you. You need to change worksheets etc as needed. I have
assumed there will be a command button (CommandButton1_Click) to initiate the
posting of data to the spreadsheet.



Public dteIndex As Integer

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
dteIndex = ComboBox1.ListIndex ' <=== Save index of combobox (0,1 or 2)
ComboBox1.Value = Format(ComboBox1.Value, "dd-mmm-yyyy") ' <== Format date
(change as required)
End Sub

Private Sub CommandButton1_Click()

If dteIndex = -1 Then ' <=== no date selected
MsgBox "Please select a date"
Exit Sub
End If

Col = dteIndex * 2 + 3 ' <=== Column C , E or G
Row = 4
Worksheets("sheet2").Select ' output on Sheet2 Change to suit your needs
With Worksheets("sheet2") ' <=== Change to suit your needs
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then ' <=== name selected
Row = Row + 1
.Cells(Row, Col) = ListBox1.List(i)
End If
Next
End With
End Sub

Private Sub UserForm_Initialize()
Worksheets("sheet1").Select
With Worksheets("sheet1")
For i = 1 To 3
ComboBox1.AddItem .Cells(i, 2) '<=== Dates in column B
Next i
For i = 1 To 5
ListBox1.AddItem .Cells(i, 1) '<=== Names in column A
Next
End With
ListBox1.MultiSelect = 1 ' <=== allow mlutiple selections
End Sub

HTH