Pre-populate userform in a loop

[mrbeanyuk]

Hi all. I have developed a basic userform and it works perfectly adding new data to the bottom. The thing is, I don't want it to add new rows. I would like it to cycle through all rows that are filtered (separate macro filters before form opens), and then pre-populate all the form fields with what is contained in the respective cell, and leave just 'Txt_Score' blank for the user to complete.

In effect, the excel auto filters based on the username of the person who is logged onto the machine. Rather than their 'score' into the sheet, I'd like them to do it in the form.

Thanks very much!

 Private Sub cmdNext_Click()
    'Copy input values to sheet.
    Dim lRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("TestSheet")
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
        .Cells(lRow, 7).Value = Me.Txt_OpObj.Value
        .Cells(lRow, 14).Value = Me.Txt_Measure.Value
        .Cells(lRow, 15).Value = Me.Txt_Calc.Value
        .Cells(lRow, 16).Value = Me.Txt_Target.Value
        .Cells(lRow, 22).Value = Me.Txt_Input.Value
        .Cells(lRow, 23).Value = Me.Txt_Score.Value
        .Cells(lRow, 26).Value = Me.Txt_Risk.Value
        .Cells(lRow, 28).Value = Me.Txt_Mitigation.Value
        .Cells(lRow, 29).Value = Me.Txt_Progress.Value
        .Cells(lRow, 30).Value = Me.Txt_RevDate.Value

    End With
    'Clear input controls.
    Me.Txt_OpObj.Value = ""
    Me.Txt_Measure.Value = ""
    Me.Txt_Calc.Value = ""
    Me.Txt_Target.Value = ""
    Me.Txt_Input.Value = ""
    Me.Txt_Score.Value = ""
    Me.Txt_Risk.Value = ""
    Me.Txt_Mitigation.Value = ""
    Me.Txt_Progress.Value = ""
    Me.Txt_RevDate.Value = ""
 
End Sub

 

[Norie]

Where's your current code that populates the userform?

 

[mrbeanyuk]

Hi. That's the bit I am unable to do. All I have is a blank form that the user completes and it adds it to a new row at the end, rather the pre-populate. Thanks

 

[Norie]

So the user would enter a name on the form, click a button to search/filter for that name and the form would be populated with the data for the first record for the name?

They would then enter the score, click another button to save it and then move onto the next record.

Is that the general idea?

 

[mrbeanyuk]

Thanks Norie.

When the user opens the spreadsheet, it automatically filters to their name and opens the form.

I would like them to enter a score, save it, move to the next record. I already have a macro attached to the close button which extracts their rows and emails them to me. The key here is that I only want filtered rows to appear in the form, and I would like the form fields below to be pre-populated in the form. Hopefully that is clear? Thanks again

 .Cells(lRow, 14).Value = Me.Txt_Measure.Value
        .Cells(lRow, 15).Value = Me.Txt_Calc.Value
        .Cells(lRow, 16).Value = Me.Txt_Target.Value
        .Cells(lRow, 22).Value = Me.Txt_Input.Value

 

[mrbeanyuk]

Any help gratefully received.