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,
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.
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.
