Easier way of copying userform fields to a workbook-CodePudding

I've created a userform where I have a "save" button which copies all of the values in the text and comment boxes to the workbook. I've included the code below but I'm wondering if there is a more efficient way of achieving this without using all the lines of code:

prod = Range("d4").Value - 1
Sheets("Latest").Select

ActiveCell.Offset(prod, 0).Select
ActiveCell.Value = 1
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Application.UserName
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Now()
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = age.Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = sex.Value
...
...
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = height.Value

I also have the same issue when loading the userform with pre-defined values. I literally have to have a line for every textbox that is to be populated:

Controls("age").Value = Format(Range("age"), "standard")
Controls("share").Value = Format(Range("share"), "percent")
Controls("salary").Value = Format(Range("salary"), "#,###")
...
...

Controls("share2").Value = Format(Range("share2"), "percent")

the code works but it just seems like it's unnecessarily long.

CodePudding user response：

The trick is to use the same name for the cells as for the controls on the user-form.

The name-syntax should help to distinguish the names from other names on the sheet, eg. input_xxx.

This is a simplified solution which you have to adjust to your needs:

Sub showForm()

'assumption all cells/controls have name with syntax input_xxx

writeValuesToUserForm

UserForm1.Show vbModal

writeValuesToSheet

End Sub

Public Sub writeValuesToUserForm()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Latest")

Dim n As Name
For Each n In ThisWorkbook.Names
    If n.Name Like "input_*" Then
        UserForm1.Controls(n.Name).Value = n.RefersToRange
    End If
Next
End Sub

Sub writeValuesToSheet()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Latest")

Dim n As Name
For Each n In ThisWorkbook.Names
    If n.Name Like "input_*" Then
        n.RefersToRange.Value = UserForm1.Controls(n.Name).Value
    End If
Next

End Sub