Prefill Exel Userform with data from a worksheet

I have a spreadsheet with multiple customers. Customer information (i.e. name, address) is entered on the last worksheet. A userform is used to fill in an invoice worksheet. How can I prefill the userform with customer information when typing the customer name?
waynerayAsked:
Who is Participating?
 
Roy CoxConnect With a Mentor Group Finance ManagerCommented:
I would use a ComboBox to select the name. See the example - select a name, the rest of the data auto fills the TextBoxes.

The code is dynamic to you can add columns of data and TextBoxes as required, simply name the TextBoxes TextBox1, TextBox2, etc.
ComboBox-Lookup.xlsm
0
 
Ryan ChongConnect With a Mentor Commented:
do you mean to populate the User Form with data from Worksheet?

if yes, then see if this is good for you?

Private Sub UserForm_Activate()
    Dim r As Integer
    r = ActiveCell.row
    txtName.Text = ActiveSheet.Cells(r, 1)
    txtAddress.Text = ActiveSheet.Cells(r, 2)
End Sub

Open in new window

29086195.xlsm
0
 
waynerayAuthor Commented:
Excellent. Thanks. What I needed.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Fabrice LambertConnect With a Mentor Fabrice LambertCommented:
I would use the userForm_initialize() event instead of UserForm_Activate(), as the later may fire more than once (eventually overwriting data) wich may not be the behavior you expect.

Also, I do not advice using objects such as ActiveWorkbook, ActiveSheet, ActiveCell ect ... as these objects are user dependant and by nature chaotic. As a developper, you don't want to use chaotic objects (Ask yourself: What will happen if the user open or alt-tab to another workbook ?).
Prefer referencing precisely the objects you intend to use:
Dim wb As Excel.Workbook
Set wb = ThisWorkbook    '// ref the workbook holding VBA code

Dim ws As Excel.Worksheet
Set ws = wb.Worksheets(1)    '// ref the 1st worksheet of the workbook holding the code

Dim rng As Excel.Range
Set rng = ws.Range("B8")    '// ref the B8 cell of the 1st worksheet

Open in new window

0
 
waynerayAuthor Commented:
Thanks Experts
0
 
Roy CoxGroup Finance ManagerCommented:
Pleased to help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.