• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Move loop to next row?

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]
 
Hi Nebu, I've uploaded a sample - but because I've had to take out the query data, some of the form is not working properly - but hopefully you can see the trail of what's happening with this. Thanks for your help
 

Attachments

  • Strategy Considerations sample.xlsm
    39 KB · Views: 0
Hi Nebu, I've uploaded a sample - but because I've had to take out the query data, some of the form is not working properly - but hopefully you can see the trail of what's happening with this. Thanks for your help
 

Attachments

  • Strategy Considerations sample.xlsm
    39 KB · Views: 0
Hi:

Please try the following code.

Code:
Private Sub ComboBoxStrategy_Change()

Dim ws As Worksheet, currRow As Long, lastRow As Long, prevAction As String, newRow As Integer
   
    'Load the actions
   
    Set ws = Worksheets("Strategies")
    lastRow = ws.UsedRange.Rows.Count
    Me.lstProvision.Visible = True
   
    With Me.lstProvision
        .Clear
            For currRow = 3 To lastRow
                newRow = currRow + 1
                    If ws.Cells(currRow, 4) = Me.ComboBoxStrategy Then
                        .AddItem ws.Cells(currRow, 5).Value
                    End If
            Next
       
        .Visible = True
    End With

End Sub
Thanks
 
Back
Top