Angelique_C
New Member
HI,
I've written code to fill a combobox list (lstProvision), dependent on selections made in a previous combobox (ComboBoxStrategy). The data to fill the "lstProvision" combobox is stored in worksheet("Strategies") column E, which I want to fill the combobox list if the text is equal to the current row cell in column D, which is selected in the previous combobox (ComboBoxStrategy). The code is not right, the next row is not being selected in the loop, only the data from cell E3 is ever filled - however the list needs to be filled by content of cells in E column, each time a match is made with the content of cells in the D column. The data in cells of the D column can appear more than once, however each time they are in a new row in column D, their next cell in column E has different data so the the combobox list"lstProvision" needs to show & select mulitple items if needed. The list in the combobox"lstProvision" also needs to clear when a new selection is made in the ComboBoxStrategy. Can anyone help?
[Private Sub ComboBoxStrategy_Change()
Dim ws As Worksheet, currRow As Long, lastRow As Long, prevAction As String, newRow As Integer
Set ws = Worksheets("Strategies")
lastRow = ws.UsedRange.Rows.Count
Me.lstProvision.Visible = True
'prevAction = ""
With Me.lstProvision
.Clear
For currRow = 3 To lastRow
newRow = currRow + 1
If Val(ws.Cells(currRow, 4)) = Val(Me.ComboBoxStrategy) Then
.AddItem ws.Cells(currRow, 5).Value
ws.Cells(currRow, 4).Offset(1, 0).Select
'prevAction = ws.Cells(currRow, 4).Value
End If
If ws.Cells(currRow, 4) <> Val(Me.ComboBoxStrategy) Then Exit For '
Next
.Visible = True
End With
End Sub]
I've written code to fill a combobox list (lstProvision), dependent on selections made in a previous combobox (ComboBoxStrategy). The data to fill the "lstProvision" combobox is stored in worksheet("Strategies") column E, which I want to fill the combobox list if the text is equal to the current row cell in column D, which is selected in the previous combobox (ComboBoxStrategy). The code is not right, the next row is not being selected in the loop, only the data from cell E3 is ever filled - however the list needs to be filled by content of cells in E column, each time a match is made with the content of cells in the D column. The data in cells of the D column can appear more than once, however each time they are in a new row in column D, their next cell in column E has different data so the the combobox list"lstProvision" needs to show & select mulitple items if needed. The list in the combobox"lstProvision" also needs to clear when a new selection is made in the ComboBoxStrategy. Can anyone help?
[Private Sub ComboBoxStrategy_Change()
Dim ws As Worksheet, currRow As Long, lastRow As Long, prevAction As String, newRow As Integer
Set ws = Worksheets("Strategies")
lastRow = ws.UsedRange.Rows.Count
Me.lstProvision.Visible = True
'prevAction = ""
With Me.lstProvision
.Clear
For currRow = 3 To lastRow
newRow = currRow + 1
If Val(ws.Cells(currRow, 4)) = Val(Me.ComboBoxStrategy) Then
.AddItem ws.Cells(currRow, 5).Value
ws.Cells(currRow, 4).Offset(1, 0).Select
'prevAction = ws.Cells(currRow, 4).Value
End If
If ws.Cells(currRow, 4) <> Val(Me.ComboBoxStrategy) Then Exit For '
Next
.Visible = True
End With
End Sub]