Easier way of copying userform fields to a workbook

user437032 0
Viewed 33

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.

userform vba excel
1 Answers

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
useryPy710
Related