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