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!
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