I have a user form that I created that allows the entry of employee information that is then saved to a worksheet. The fields are:
First Name
Last Name
Hire Date
Hourly Pay Rate
Title / Position
Mon Start
Mon End
(continues through Sun End)
I have attached a copy of the code for this user form.
I want to create a duplicate user form that will allow a search by employee first or last name and return all of the values for that employee from the worksheet back into the user form to edit any of the entries and use a Save Changes command button to return the changes back to the worksheet.
Also, if the search produces multiple matches, display First and Last name in a list to select the correct employee info to return.
Thanks in advance for your assistance and let me know if you need any other info.
Private Sub cmdAddEmp_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Employee Info")
'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
If Trim(Me.txtFName.Value) = "" Then
Me.txtFName.SetFocus
MsgBox "Please enter an employee name"
Exit Sub
End If
'copy the data to the database
'use protect and unprotect lines,
' with your password
' if worksheet is protected
With ws
' .Unprotect Password:="password"
.Cells(iRow, 1).Value = Me.txtFName.Value
.Cells(iRow, 2).Value = Me.txtLName.Value
.Cells(iRow, 3).Value = Me.txtHireDate.Value
.Cells(iRow, 4).Value = Me.txtPayRate.Value
.Cells(iRow, 5).Value = Me.txtTitle.Value
.Cells(iRow, 6).Value = Me.txtMonIn.Value
.Cells(iRow, 7).Value = Me.txtMonOut.Value
.Cells(iRow, 8).Value = Me.txtTueIn.Value
.Cells(iRow, 9).Value = Me.txtTueOut.Value
.Cells(iRow, 10).Value = Me.txtWedIn.Value
.Cells(iRow, 11).Value = Me.txtWedOut.Value
.Cells(iRow, 12).Value = Me.txtThuIn.Value
.Cells(iRow, 13).Value = Me.txtThuOut.Value
.Cells(iRow, 14).Value = Me.txtFriIn.Value
.Cells(iRow, 15).Value = Me.txtFriOut.Value
.Cells(iRow, 16).Value = Me.txtSatIn.Value
.Cells(iRow, 17).Value = Me.txtSatIn.Value
.Cells(iRow, 18).Value = Me.txtSunIn.Value
.Cells(iRow, 19).Value = Me.txtSunOut.Value
' .Protect Password:="password"
End With
'clear the data
Me.txtFName.Value = ""
Me.txtLName.Value = ""
Me.txtHireDate.Value = ""
Me.txtPayRate.Value = ""
Me.txtTitle.Value = ""
Me.txtMonIn.Value = ""
Me.txtMonOut.Value = ""
Me.txtTueIn.Value = ""
Me.txtTueOut.Value = ""
Me.txtWedIn.Value = ""
Me.txtWedOut.Value = ""
Me.txtThuIn.Value = ""
Me.txtThuOut.Value = ""
Me.txtFriIn.Value = ""
Me.txtFriOut.Value = ""
Me.txtSatIn.Value = ""
Me.txtSatIn.Value = ""
Me.txtSunIn.Value = ""
Me.txtSunOut.Value = ""
Me.txtFName.SetFocus
End Sub
Tom
Bookmarks