Comboboxes

[Lenna]

I’ve done some reading about comboboxes but need help.

I need to populate a combobox (Sheet1) with items from (Sheet2). Sheet2 has several lists starting in A1 the number of lists will vary. All lists are three columns apart and one column wide.

Sheet2

1/2/2013

2/5/2013

3/6/2013

4/7/2013

5/7/2013

Name1

Name2

Name3

Name4

Name5

<tbody>
</tbody>

I would like to display each date + Name as a combobox item.

When I select a combobox item it would like for that item to be selected on Sheet2. Is it possible?

Please suggest some code.

Thanks,

Lenna

 

[Lenna]

Yes please, would that change the code?

 

[Lenna]

This one

Private Sub cb1_Change()


End Sub

 

[Worf]

Yes please, would that change the code?

It will change the code but this is not a problem. I will rewrite it using the change event, meanwhile you may test the current version...

 

[Lenna]

Ad(), Private Sub UserForm_Initialize() and Private Sub cb1_Exit all go in UserForm module, correct?

I've already have this code that will show the userform:

Option Explicit


Private Sub Workbook_Open()


UserForm1.Show


End Sub

 

[Worf]

Ad(), Private Sub UserForm_Initialize() and Private Sub cb1_Exit all go in UserForm module, correct?

I've already have this code that will show the userform:

Option Explicit

Private Sub Workbook_Open()

UserForm1.Show
End Sub

Correct, all that in user form module.
If changes are necessary to the workbook open procedure I will let you know.

 

[Lenna]

Yes, it does work!

Obrigado por toda sua ajuda. você é um gênio!

 

[Worf]

Your portuguese is very good...

I must go offline now but will return tomorrow with the new version.

 

[Lenna]

The code was working great until I added the second combobox (cb2) and now Exit routine is failing. Here is what I have.

Dim ad()
Dim al()

Private Sub cb1_Exit(ByVal Cancel As MSForms.ReturnBoolean)


' triggered by leaving the combobox
'Worksheets("ClassI").Range(ad(cb1.ListIndex + 1)).Resize(, 3).Select


Worksheets("ClassI").Range(ad(cb1.ListIndex + 1)).Select
Range(Selection, Selection.End(xlDown)).Resize(, 3).Select

Private Sub cb2_Exit(ByVal Cancel As MSForms.ReturnBoolean)


' triggered by leaving the combobox
Worksheets("ClassII").Range(al(cb2.ListIndex + 1)).Resize(, 3).Select


'Worksheets("ClassI").Range(ad(cb1.ListIndex + 1)).Select
'Range(Selection, Selection.End(xlDown)).Resize(, 3).Select


End Sub

Private Sub UserForm_Initialize()




Dim i%, ws As Worksheet, ws2 As Worksheet
Dim j%


Set ws = Worksheets("ClassI")
Set ws2 = Worksheets("ClassII")


cb1.clear
cb2.clear
'initilizing cb1


MsgBox cb1.ListCount, vbInformation, "Items in cb1"
For i = 1 To ws.Cells(1, Columns.Count).End(xlToLeft).Column Step 4
    cb1.AddItem ws.Cells(1, i).Value & " - " & ws.Cells(1, i).Offset(2).Value  ' populate
    ReDim Preserve ad(cb1.ListCount)
    ad(cb1.ListCount) = ws.Cells(1, i).Offset(2).Address    ' store location
Next
cb1.ListIndex = 0
MsgBox "Complete" & vbLf & cb1.ListCount & " items added", vbInformation, "i= " & i


'initilizing cb2


MsgBox cb2.ListCount, vbInformation, "Items in cb2"
For j = 1 To ws2.Cells(1, Columns.Count).End(xlToLeft).Column Step 4
    cb2.AddItem ws2.Cells(1, j).Value & " - " & ws2.Cells(1, j).Offset(2).Value  ' populate
    ReDim Preserve al(cb2.ListCount)
    al(cb2.ListCount) = ws2.Cells(1, i).Offset(2).Address    ' store location
Next
cb2.ListIndex = 0
MsgBox "Complete" & vbLf & cb2.ListCount & " items added", vbInformation, "j= " & j




End Sub

 

[Worf]

It seems to be working now. Would you like to use the change event instead?

Dim ad(), al(), i%, ws As Worksheet, ws2 As Worksheet, j%


Private Sub cb1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim r As Range
ws.Activate
Set r = ws.Range(ad(cb1.ListIndex + 1))
Range(r, r.End(xlDown)).Resize(, 3).Select
End Sub


Private Sub cb2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ws2.Activate
ws2.Range(al(cb2.ListIndex + 1)).Resize(, 3).Select
End Sub


Private Sub UserForm_Initialize()
Set ws = Worksheets("ClassI")
Set ws2 = Worksheets("ClassII")
cb1.Clear
cb2.Clear
'initializing cb1
MsgBox cb1.ListCount, vbInformation, "Items in cb1"
For i = 1 To ws.Cells(1, Columns.Count).End(xlToLeft).Column Step 4
    cb1.AddItem ws.Cells(1, i).Value & " - " & ws.Cells(1, i).Offset(2).Value  ' populate
    ReDim Preserve ad(cb1.ListCount)
    ad(cb1.ListCount) = ws.Cells(1, i).Offset(2).Address    ' store location
Next
cb1.ListIndex = 0
MsgBox "Complete" & vbLf & cb1.ListCount & " items added", vbInformation, "i= " & i
'initializing cb2
MsgBox cb2.ListCount, vbInformation, "Items in cb2"
For j = 1 To ws2.Cells(1, Columns.Count).End(xlToLeft).Column Step 4
    cb2.AddItem ws2.Cells(1, j).Value & " - " & ws2.Cells(1, j).Offset(2).Value  ' populate
    ReDim Preserve al(cb2.ListCount)
    al(cb2.ListCount) = ws2.Cells(1, j).Offset(2).Address    ' store location
Next
cb2.ListIndex = 0
MsgBox "Complete" & vbLf & cb2.ListCount & " items added", vbInformation, "j= " & j
End Sub

 

[Lenna]

I think the change event would be less error prone. yes, please.

Thanks,

Lena