Results 1 to 6 of 6

User Form to execute search and return all values to the user form for editing

Threaded View

  1. #1
    Registered User
    Join Date
    04-20-2013
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    29

    User Form to execute search and return all values to the user form for editing

    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
    Attached Files Attached Files
    Last edited by Leith Ross; 05-11-2013 at 04:57 AM. Reason: Copied code from text file and placed it in the post.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1