Using combobox to pass data from an Excel spread sheet to text boxes on userform

Stewie840 · #1 12-15-2015, 03:05 PM

This is my first post so I hope someone understands my problem
I have an excel worksheet which lists employees names, dob and employee numbers

A B C D
1 EmployID FName GName DOB
2 123456 Jones James 19/02/1976
3 345678 Smith Roger 04/05/1955
4 678901 Evans Jon 16/11/1964

My report is a word document (2010) which uses a userform to collect information from the spread sheet and then pass the data to bookmarks (legacy form fields) in the document.
On my frmEmployee userform I have a combo box (cboID) which is linked to the excel spread sheet when I initialize the form and I can select the employee’s ID number. How do I then pass the other information ie FamilyName, GivenName & DOB to text boxes on the same form (txtFName, txtGName & txtDOB).
Thanking you in advance

macropod · #2 12-15-2015, 04:57 PM

Is there a reason for not using mailmerge for this, instead of formfields?

Stewie840 · #3 12-15-2015, 05:14 PM

G'day Paul

I'm not using mailmerge because there are other userforms with other information I have to imput from other sources. This Employee report document is also used by other members of my team over the network.

Cheers

Stewie

macropod · #4 12-15-2015, 05:20 PM

OK, so you're going to have to automate Word from Excel but, before doing that, you'd need to know whether the target document has to already be open or if you want to create a new one from an appropriate template.

Stewie840 · #5 12-15-2015, 05:36 PM

Paul

I've created a .dotm template and use the Auto_New procedure to open the form.

Cheers

Stewie

macropod · #6 12-15-2015, 06:00 PM

In that case, you should be able to create a new document & populate the formfields using code like:

Code:

Dim wdApp As Object, wdDoc As Object
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If wdApp Is Nothing Then
  Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0
With wdApp
  Set wdDoc = .Documents.Add("template path & name")
  With wdDoc
    .Formfields("EmployID").Result = Me.cboID.Value
    .Formfields("FName").Result = Me.txtFName.Value
    .Formfields("GName").Result = Me.txtGName.Value
    .Formfields("DOB").Result = Me.txtDOB.Value
  End With
End With

Formfields named as EmployID, FName, GName & DOB are assumed.

Stewie840 · #7 12-15-2015, 08:12 PM

Paul

Thanks for that. I seemed to have misled you. The data I am passing to the userform is from an Excel file, as in my first post. cboID selects the EmployeeID from the excel file (userform_initialize) add items, and then I want the corresponding information to populate the text boxes.

I hope this is clearer, and I do appreciate your time in helping me.

Cheer

Stewie

PS how do I put code into these posts?

macropod · #8 12-15-2015, 09:34 PM

So you want to populate the Excel userfom - via the UserID - and you're not concerned with populating the formfields in the Word document at this stage?

Stewie840 · #9 12-15-2015, 09:44 PM

Paul

I am populating Employee ID via on a Word userform from my data source which happens to be an Excel spread sheet. It probably sounds a bit convoluted.

Cheers

Stewie

macropod · #10 12-15-2015, 09:47 PM

So what is the code you're using for:

Quote:

Originally Posted by Stewie840

On my frmEmployee userform I have a combo box (cboID) which is linked to the excel spread sheet when I initialize the form and I can select the employee’s ID number.

Stewie840 · #11 12-15-2015, 10:00 PM

This is the code I have come up with so far but I get a runtime error if I start typing something that is not in the list.

Code:

 
Private Sub UserForm_Initialize()
 
Dim xlApp As Object
Dim xlWb As Object
Dim xlSht As Object
Dim lastRow As Integer
Dim i As Long
 
'Centre user form in Application
Me.StartUpPosition = 0
Me.Top = Application.Top + (Application.Height / 2) - (Me.Height / 2)
Me.Left = Application.Left + (Application.Width / 2) - (Me.Width / 2)
 
'Populate Employee Details from Excel file
Set xlApp = CreateObject("Excel.Application")
 
Set xlWb = xlApp.Workbooks.Open '("Path\Employees.xlsx")
Set xlSht = xlWb.Worksheets(1)
 
cboID.ColumnCount = 4
 
With xlSht
lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
 
With Me.cboID
For i = 2 To lastRow
.AddItem xlSht.Cells(i, 1)
.List(.ListCount - 1, 1) = xlSht.Cells(i, 2)
.List(.ListCount - 1, 2) = xlSht.Cells(i, 3)
.List(.ListCount - 1, 3) = xlSht.Cells(i, 4)
Next i
End With
 
xlApp.DisplayAlerts = False
xlWb.Close
Set xlSht = Nothing
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub
 
Private Sub cboID_Change()
txtFName.Text = Me.cboID.Column(1)
txtGName.Text = Me.cboID.Column(2)
txtDOB.Text = Me.cboID.Column(3)
End Sub

It works if the user starts typing the correct numbers into the cboID.

If they do get a run-time error I just want it to reset and try again.

I am still a novice at all this but have been trolling the internet for days for a solution. Once again I appreciate your help.

Cheers

Stewie

macropod · #12 12-15-2015, 10:52 PM

Quote:

Originally Posted by Stewie840

I get a runtime error if I start typing something that is not in the list.

Perhaps, then you need to consider why you're using a combobox instead of a ListBox, so they can only choose from the valid records.

Stewie840 · #13 12-16-2015, 05:13 PM

Paul

I will explore that option.

Cheers

Stewie

Stewie840 · #14 12-17-2015, 08:32 PM

Quote:

Originally Posted by macropod

Perhaps, then you need to consider why you're using a combobox instead of a ListBox, so they can only choose from the valid records.

Paul, the reason I am not using a list box is...there are around 2000 IDs and I want the user to start typing the ID number.

If they accidentially type the wrong number I get the run-time error(381).

So now the question is how do I handle that error and reset the form to type the correct number in.

Hope that is clearer than mud.

Cheers

Stewie

macropod · #15 12-17-2015, 10:13 PM

Scrolling through a sorted list would hardly take much longer than typing. That said, what you require if you're going to allow free input is some form of error-trapping. For that, you could use the keypress event to test each character as it's input to prevent invalid strings being input, or you wait till the string has been then test for its presence amongst the other entries and, if not found, delete the entry and return the user to the combobox instead of continuing with the processing.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Value of Combobox in other userform	Vibov	Excel Programming	4	11-19-2015 04:12 AM
Pass combobox content to header	wpryan	Word VBA	3	07-17-2015 01:44 AM
How To Open an Macro From 2003 Excel in 2013 Excel Spread Sheet?	ADubin	Excel Programming	3	02-08-2015 04:57 AM
Mail merge, text boxes, and two documents per sheet!	MelissaAnnie	Mail Merge	2	09-24-2014 03:46 PM
Condensing a spread sheet	hawkeyefxr	Excel	4	08-22-2012 05:17 AM