Pre-populate userform in a loop

mrbeanyuk

Board Regular
Joined
Nov 30, 2005
Messages
214
Office Version
  1. 365
Platform
  1. Windows
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!


Code:
 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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Where's your current code that populates the userform?
 
Upvote 0
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 :)
 
Upvote 0
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?
 
Upvote 0
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

Code:
 .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
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,260
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top