Populate a Userform with VBA

The following procedure will show you how to populate an Excel userform with the data in a file and send the edited data back to the database.  It is very handy as in this example simply putting a key word in the userform and hitting return will automatically populate the userform.

 

The key to setting up a good userform is structure.  The items in your userform should ideally be logically laid out so that when you go to write the coding to send the userform information to your data set a sequential loop can be used rather than something which is un-ordered.  For example,

Userform populate

Textbox1 in your userform should be the information in Column 1 of your database.  Textbox2 should be in column 2 and so on.  Sounds logical but you would be surprised how few userforms are set up with this simple logic in mind.  Maybe they start out with good intentions and the project changes with time?

 

The following is our demonstration userfom.

 

Userform populate vba

 In this example we are going to add some very neat logic.  If you enter a valid Registration number (textbox1) and press ENTER, the userform will pre populate.

 

In the above example I entered 101 into Registration number and hit enter and the form pre populated.  The procedure to do this is as follows.

 

Option Explicit  
Private Sub editstudent1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) 
    If KeyCode=vbKeyReturn Then 
        Findit 
    End If 
End Sub

Where the procedure called Findit is called after Enter is clicked.

Private Sub Findit() 'Find and populate the records with Excel VBA
Dim fnd As Range
Dim Search As String
Dim sh As Worksheet
Dim i As Integer
  Set sh=Sheet2
Search=editstudent1.Text
Set fnd=sh.Columns("A:A").Find(Search, , , xlWhole)
    If fnd Is Nothing Then
        MsgBox "No Person Found", , "Error"
        editstudent1.Text=""
        frmeditrecord.Hide
    Else
        For i=2 To 13 'There are 13 items in the userform.
            frmeditrecord.Controls("editstudent" & i).Text=sh.Cells(fnd.Row, i).Value
        Next i
    End If
End Sub

The above Findit procedure checks for the items in texbox1 which is called editstudent1.  If the items is found then it populated the userform with the records from that unique registration number.  If it fails to find the registration number an error message appears.


After you have edited the userform the form has an Update button.  This button when clicked will send the data back to the sheet with the data on it and update it with any changes you have made.  

Private Sub cmdUpdate_Click() 'Push data back to the tab with Excel VBA
Dim fnd As Range
Dim Search As String
Dim sh As Worksheet
Dim i As Integer
Dim ctl As Object Set sh=Sheet2
Search=editstudent1.Text

Set fnd=sh.Columns("A:A").Find(Search, , , xlWhole)
      For i=2 To 13
         sh.Cells(fnd.Row, i).Value=frmeditrecord.Controls("editstudent" & i).Text
    Next i
    'Clear Form controls
    For Each ctl In Me.Controls
       If TypeName(ctl)="TextBox" Then ctl.Value=Null
    Next ctl
End Sub

 

As you can see the above procedure is simply the revers of the procedure to pull the information into the userform.  

 

The attached file shows a working example of this procedure.  It should be adaptable to so may other userform examples as the coding is simple and relatively easy to follow in my opinion.

 

UserformExample.xlsm