+ Reply to Thread
Results 1 to 5 of 5

VBA Userform Multiple Textboxes to Find Record

  1. 04-06-2016, 03:01 PM #1
    chamowllie01
    chamowllie01 is offline
    Registered User
    Join Date
    03-23-2016
    Location
    Jacksonville, Florida
    MS-Off Ver
    MS Office 2010
    Posts
    3

    VBA Userform Multiple Textboxes to Find Record

    I am very new to VBA and I have combed the board for 2 days trying to figure this out to no avail. I have a workbook with 2 userforms and 2 data sheets. The EMP userform (saves to EMPdata sheet) has an Emp No TB field and a LAST NAME TB field. I want to be able to search EITHER of these fields on the EMPdata sheet to bring the record to the UserForm.

    The code I am using Private Sub cmdLOOKUP_Click(). It works for Test 2 which looks up the last name, but not for the EMPNO. It just erases the field.

    Private Sub cmdLOOKUP_Click()

    Dim test2 As String
    Dim test1 As String
    Dim FoundRange As Range
    Dim FoundRange1 As Range

    test1 = EMPNOE.Value
    test2 = txtLASTNAME.Value

    Worksheets("EMPData").Activate

    Set FoundRange = Sheets("Empdata").Range("D:D").Find(What:=test2, LookIn:=xlFormulas, LookAt:=xlWhole)

    If FoundRange Is Nothing Then
    Response = MsgBox("No match for " & Me.txtLASTNAME.Value, , vbOK)

    If Response = vbOK Then
    cmdEMPTY
    End If
    Else
    EMPNOE.Text = FoundRange.Offset(0, -3).Value
    txtLASTNAME.Text = FoundRange.Offset(0, 0).Value
    FIRSTNAME.Text = FoundRange.Offset(0, -2).Value
    MName.Text = FoundRange.Offset(0, -1).Value
    SOCIALSECNO.Text = FoundRange.Offset(0, 1).Value
    cboEMP.Value = FoundRange.Offset(0, 2).Value
    DATEOFHIRE.Text = FoundRange.Offset(0, 3).Value
    DOT.Text = FoundRange.Offset(0, 4).Value
    cboSEX.Value = FoundRange.Offset(0, 5).Value
    POSITION.Text = FoundRange.Offset(0, 6).Value
    cboWCCLASS.Value = FoundRange.Offset(0, 7).Value
    cboMARITAL.Value = FoundRange.Offset(0, 8).Value
    cboPAYROLL.Value = FoundRange.Offset(0, 9).Value
    STARTPAY.Text = FoundRange.Offset(0, 10).Value
    CURRENTPAY.Text = FoundRange.Offset(0, 11).Value
    RAISEDATE.Text = FoundRange.Offset(0, 12).Value
    RAISETYPE.Text = FoundRange.Offset(0, 13).Value
    DOB.Text = FoundRange.Offset(0, 14).Value
    DLSTATE.Text = FoundRange.Offset(0, 15).Value
    DLNO.Text = FoundRange.Offset(0, 16).Value
    W2STATUS.Text = FoundRange.Offset(0, 17).Value
    W2ALLOW.Text = FoundRange.Offset(0, 18).Value
    W1ADDWH.Text = FoundRange.Offset(0, 19).Value
    HMADD.Text = FoundRange.Offset(0, 20).Value
    HOMECITY.Text = FoundRange.Offset(0, 21).Value
    STATE.Text = FoundRange.Offset(0, 22).Value
    ZIPCODE.Text = FoundRange.Offset(0, 23).Value
    HOMEPHONE.Text = FoundRange.Offset(0, 24).Value
    MOBILEPHONE.Text = FoundRange.Offset(0, 25).Value
    EC1.Text = FoundRange.Offset(0, 26).Value
    EC2.Text = FoundRange.Offset(0, 27).Value
    EC3.Text = FoundRange.Offset(0, 28).Value
    EC4.Text = FoundRange.Offset(0, 29).Value
    EC5.Text = FoundRange.Offset(0, 30).Value
    EC6.Text = FoundRange.Offset(0, 31).Value
    EC7.Text = FoundRange.Offset(0, 32).Value
    EC8.Text = FoundRange.Offset(0, 33).Value
    EC9.Text = FoundRange.Offset(0, 34).Value
    EC10.Text = FoundRange.Offset(0, 35).Value
    WORKSHEETNOTES.Text = FoundRange.Offset(0, 36).Value
    IRA1.Text = FoundRange.Offset(0, 37).Value
    IRA2.Text = FoundRange.Offset(0, 38).Value
    cboIRA.Value = FoundRange.Offset(0, 39).Value
    IRA4.Text = FoundRange.Offset(0, 40).Value
    CAFHIDate.Text = FoundRange.Offset(0, 41).Value
    CAFHCovType.Text = FoundRange.Offset(0, 42).Value
    CAFHOccCode.Text = FoundRange.Offset(0, 43).Value
    CAFHInsRate.Text = FoundRange.Offset(0, 44).Value
    CAFDentDate.Text = FoundRange.Offset(0, 45).Value
    CAFDentType.Text = FoundRange.Offset(0, 46).Value
    CAFDentRate.Text = FoundRange.Offset(0, 47).Value
    CAFAccDate.Text = FoundRange.Offset(0, 48).Value
    CAFAccRate.Text = FoundRange.Offset(0, 49).Value
    CAFHospDate.Text = FoundRange.Offset(0, 50).Value
    CAFHospRate.Text = FoundRange.Offset(0, 51).Value
    CAFCancerDate.Text = FoundRange.Offset(0, 52).Value
    CAFCancerRate.Text = FoundRange.Offset(0, 53).Value
    CAFCritInsDate.Text = FoundRange.Offset(0, 54).Value
    CAFCritInsRate.Text = FoundRange.Offset(0, 55).Value
    CAFDisInsDate.Text = FoundRange.Offset(0, 56).Value
    CAFDisCovType.Text = FoundRange.Offset(0, 57).Value
    CAFDisInsRate.Text = FoundRange.Offset(0, 58).Value
    CAFLifeDate.Text = FoundRange.Offset(0, 59).Value
    CAFLifeType.Text = FoundRange.Offset(0, 60).Value
    CAFLifeRate.Text = FoundRange.Offset(0, 61).Value
    TERMHealthCX.Text = FoundRange.Offset(0, 62).Value
    TERMDentalCX.Text = FoundRange.Offset(0, 63).Value
    TERMAccCX.Text = FoundRange.Offset(0, 64).Value
    TERMHospCX.Text = FoundRange.Offset(0, 65).Value
    TERMCancerCX.Text = FoundRange.Offset(0, 66).Value
    TERMCritCX.Text = FoundRange.Offset(0, 67).Value
    TERMDisabCX.Text = FoundRange.Offset(0, 68).Value
    TERMLifeCX.Text = FoundRange.Offset(0, 69).Value
    TERMCobraActive.Text = FoundRange.Offset(0, 70).Value
    TERMCobraCX.Text = FoundRange.Offset(0, 71).Value
    End If

    If txtLASTNAME.Text = "" Then

    Set FoundRange1 = Sheets("Empdata").Range("A:A").Find(What:=test1, LookIn:=xlFormulas, LookAt:=xlWhole)

    If FoundRange1 Is Nothing Then
    Response = MsgBox("No match for " & Me.EMPNOE.Value, , vbOK)

    If Response = vbOK Then
    cmdEMPTY
    End If
    Else
    EMPNOE.Text = FoundRange.Offset(0, -3).Value
    txtLASTNAME.Text = FoundRange.Offset(0, 0).Value
    FIRSTNAME.Text = FoundRange.Offset(0, -2).Value
    MName.Text = FoundRange.Offset(0, -1).Value
    SOCIALSECNO.Text = FoundRange.Offset(0, 1).Value
    cboEMP.Value = FoundRange.Offset(0, 2).Value
    DATEOFHIRE.Text = FoundRange.Offset(0, 3).Value
    DOT.Text = FoundRange.Offset(0, 4).Value
    cboSEX.Value = FoundRange.Offset(0, 5).Value
    POSITION.Text = FoundRange.Offset(0, 6).Value
    cboWCCLASS.Value = FoundRange.Offset(0, 7).Value
    cboMARITAL.Value = FoundRange.Offset(0, 8).Value
    cboPAYROLL.Value = FoundRange.Offset(0, 9).Value
    STARTPAY.Text = FoundRange.Offset(0, 10).Value
    CURRENTPAY.Text = FoundRange.Offset(0, 11).Value
    RAISEDATE.Text = FoundRange.Offset(0, 12).Value
    RAISETYPE.Text = FoundRange.Offset(0, 13).Value
    DOB.Text = FoundRange.Offset(0, 14).Value
    DLSTATE.Text = FoundRange.Offset(0, 15).Value
    DLNO.Text = FoundRange.Offset(0, 16).Value
    W2STATUS.Text = FoundRange.Offset(0, 17).Value
    W2ALLOW.Text = FoundRange.Offset(0, 18).Value
    W1ADDWH.Text = FoundRange.Offset(0, 19).Value
    HMADD.Text = FoundRange.Offset(0, 20).Value
    HOMECITY.Text = FoundRange.Offset(0, 21).Value
    STATE.Text = FoundRange.Offset(0, 22).Value
    ZIPCODE.Text = FoundRange.Offset(0, 23).Value
    HOMEPHONE.Text = FoundRange.Offset(0, 24).Value
    MOBILEPHONE.Text = FoundRange.Offset(0, 25).Value
    EC1.Text = FoundRange.Offset(0, 26).Value
    EC2.Text = FoundRange.Offset(0, 27).Value
    EC3.Text = FoundRange.Offset(0, 28).Value
    EC4.Text = FoundRange.Offset(0, 29).Value
    EC5.Text = FoundRange.Offset(0, 30).Value
    EC6.Text = FoundRange.Offset(0, 31).Value
    EC7.Text = FoundRange.Offset(0, 32).Value
    EC8.Text = FoundRange.Offset(0, 33).Value
    EC9.Text = FoundRange.Offset(0, 34).Value
    EC10.Text = FoundRange.Offset(0, 35).Value
    WORKSHEETNOTES.Text = FoundRange.Offset(0, 36).Value
    IRA1.Text = FoundRange.Offset(0, 37).Value
    IRA2.Text = FoundRange.Offset(0, 38).Value
    cboIRA.Value = FoundRange.Offset(0, 39).Value
    IRA4.Text = FoundRange.Offset(0, 40).Value
    CAFHIDate.Text = FoundRange.Offset(0, 41).Value
    CAFHCovType.Text = FoundRange.Offset(0, 42).Value
    CAFHOccCode.Text = FoundRange.Offset(0, 43).Value
    CAFHInsRate.Text = FoundRange.Offset(0, 44).Value
    CAFDentDate.Text = FoundRange.Offset(0, 45).Value
    CAFDentType.Text = FoundRange.Offset(0, 46).Value
    CAFDentRate.Text = FoundRange.Offset(0, 47).Value
    CAFAccDate.Text = FoundRange.Offset(0, 48).Value
    CAFAccRate.Text = FoundRange.Offset(0, 49).Value
    CAFHospDate.Text = FoundRange.Offset(0, 50).Value
    CAFHospRate.Text = FoundRange.Offset(0, 51).Value
    CAFCancerDate.Text = FoundRange.Offset(0, 52).Value
    CAFCancerRate.Text = FoundRange.Offset(0, 53).Value
    CAFCritInsDate.Text = FoundRange.Offset(0, 54).Value
    CAFCritInsRate.Text = FoundRange.Offset(0, 55).Value
    CAFDisInsDate.Text = FoundRange.Offset(0, 56).Value
    CAFDisCovType.Text = FoundRange.Offset(0, 57).Value
    CAFDisInsRate.Text = FoundRange.Offset(0, 58).Value
    CAFLifeDate.Text = FoundRange.Offset(0, 59).Value
    CAFLifeType.Text = FoundRange.Offset(0, 60).Value
    CAFLifeRate.Text = FoundRange.Offset(0, 61).Value
    TERMHealthCX.Text = FoundRange.Offset(0, 62).Value
    TERMDentalCX.Text = FoundRange.Offset(0, 63).Value
    TERMAccCX.Text = FoundRange.Offset(0, 64).Value
    TERMHospCX.Text = FoundRange.Offset(0, 65).Value
    TERMCancerCX.Text = FoundRange.Offset(0, 66).Value
    TERMCritCX.Text = FoundRange.Offset(0, 67).Value
    TERMDisabCX.Text = FoundRange.Offset(0, 68).Value
    TERMLifeCX.Text = FoundRange.Offset(0, 69).Value
    TERMCobraActive.Text = FoundRange.Offset(0, 70).Value
    TERMCobraCX.Text = FoundRange.Offset(0, 71).Value
    End If
    End If

    End Sub
    Private Sub cmdEMPTY()
    EMPNOE.Text = ""
    txtLASTNAME.Text = ""
    FIRSTNAME.Text = ""
    MName.Text = ""
    SOCIALSECNO.Text = ""
    cboEMP.Value = ""
    DATEOFHIRE.Text = ""
    DOT.Text = ""
    cboSEX.Value = ""
    POSITION.Text = ""
    cboWCCLASS.Value = ""
    cboMARITAL.Value = ""
    cboPAYROLL.Value = ""
    STARTPAY.Text = ""
    CURRENTPAY.Text = ""
    RAISEDATE.Text = ""
    RAISETYPE.Text = ""
    DOB.Text = ""
    DLSTATE.Text = ""
    DLNO.Text = ""
    W2STATUS.Text = ""
    W2ALLOW.Text = ""
    W1ADDWH.Text = ""
    HMADD.Text = ""
    HOMECITY.Text = ""
    STATE.Text = ""
    ZIPCODE.Text = ""
    HOMEPHONE.Text = ""
    MOBILEPHONE.Text = ""
    EC1.Text = ""
    EC2.Text = ""
    EC3.Text = ""
    EC4.Text = ""
    EC5.Text = ""
    EC6.Text = ""
    EC7.Text = ""
    EC8.Text = ""
    EC9.Text = ""
    EC10.Text = ""
    WORKSHEETNOTES.Text = ""
    IRA1.Text = ""
    IRA2.Text = ""
    cboIRA.Value = ""
    IRA4.Text = ""
    CAFHIDate.Text = ""
    CAFHCovType.Text = ""
    CAFHOccCode.Text = ""
    CAFHInsRate.Text = ""
    CAFDentDate.Text = ""
    CAFDentType.Text = ""
    CAFDentRate.Text = ""
    CAFAccDate.Text = ""
    CAFAccRate.Text = ""
    CAFHospDate.Text = ""
    CAFHospRate.Text = ""
    CAFCancerDate.Text = ""
    CAFCancerRate.Text = ""
    CAFCritInsDate.Text = ""
    CAFCritInsRate.Text = ""
    CAFDisInsDate.Text = ""
    CAFDisCovType.Text = ""
    CAFDisInsRate.Text = ""
    CAFLifeDate.Text = ""
    CAFLifeType.Text = ""
    CAFLifeRate.Text = ""
    TERMHealthCX.Text = ""
    TERMDentalCX.Text = ""
    TERMAccCX.Text = ""
    TERMHospCX.Text = ""
    TERMCancerCX.Text = ""
    TERMCritCX.Text = ""
    TERMDisabCX.Text = ""
    TERMLifeCX.Text = ""
    TERMCobraActive.Text = ""
    TERMCobraCX.Text = ""
    txtLASTNAME.SetFocus
    End Sub

    File is attached.

    Thank you so much in advance for your assistance.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-06-2016, 04:41 PM #2
    Bernie Deitrick
    Bernie Deitrick is offline
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,944

    Re: VBA Userform Multiple Textboxes to Find Record

    I think the main reason is that you are looking for the Employee Number in column A, but then use the same offsets as when you looked fopr the LastName in column D. To fix it, try changing the found cell back to column D, to allow your offsets to remain unchanged.


    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.
    Last edited by Bernie Deitrick; 04-06-2016 at 04:44 PM.
    Bernie Deitrick
    Excel MVP 2000-2010
    Reply With QuoteRegister To Reply
  3. 04-07-2016, 08:36 AM #3
    chamowllie01
    chamowllie01 is offline
    Registered User
    Join Date
    03-23-2016
    Location
    Jacksonville, Florida
    MS-Off Ver
    MS Office 2010
    Posts
    3

    Re: VBA Userform Multiple Textboxes to Find Record

    Your fix was spot on. I had to Dim the Response and change the FoundRange to FoundRange1 in the second part but it works perfectly! Thank you a million times over for your help! Have a wonderful day!

    Here is the revised code:
    Private Sub cmdLOOKUP_Click()

    Dim test2 As String
    Dim test1 As String
    Dim FoundRange As Range
    Dim FoundRange1 As Range
    Dim response As String


    test1 = EMPNOE.Value
    test2 = txtLASTNAME.Value

    Worksheets("EMPData").Activate

    Set FoundRange = Sheets("Empdata").Range("D:D").Find(What:=test2, LookIn:=xlFormulas, LookAt:=xlWhole)

    If FoundRange Is Nothing Then
    response = MsgBox("No match for " & Me.txtLASTNAME.Value, , vbOK)

    If response = vbOK Then
    cmdEMPTY
    End If
    Else
    EMPNOE.Text = FoundRange.Offset(0, -3).Value
    txtLASTNAME.Text = FoundRange.Offset(0, 0).Value
    FIRSTNAME.Text = FoundRange.Offset(0, -2).Value
    MName.Text = FoundRange.Offset(0, -1).Value
    SOCIALSECNO.Text = FoundRange.Offset(0, 1).Value
    cboEMP.Value = FoundRange.Offset(0, 2).Value
    DATEOFHIRE.Text = FoundRange.Offset(0, 3).Value
    DOT.Text = FoundRange.Offset(0, 4).Value
    cboSEX.Value = FoundRange.Offset(0, 5).Value
    POSITION.Text = FoundRange.Offset(0, 6).Value
    cboWCCLASS.Value = FoundRange.Offset(0, 7).Value
    cboMARITAL.Value = FoundRange.Offset(0, 8).Value
    cboPAYROLL.Value = FoundRange.Offset(0, 9).Value
    STARTPAY.Text = FoundRange.Offset(0, 10).Value
    CURRENTPAY.Text = FoundRange.Offset(0, 11).Value
    RAISEDATE.Text = FoundRange.Offset(0, 12).Value
    RAISETYPE.Text = FoundRange.Offset(0, 13).Value
    DOB.Text = FoundRange.Offset(0, 14).Value
    DLSTATE.Text = FoundRange.Offset(0, 15).Value
    DLNO.Text = FoundRange.Offset(0, 16).Value
    W2STATUS.Text = FoundRange.Offset(0, 17).Value
    W2ALLOW.Text = FoundRange.Offset(0, 18).Value
    W1ADDWH.Text = FoundRange.Offset(0, 19).Value
    HMADD.Text = FoundRange.Offset(0, 20).Value
    HOMECITY.Text = FoundRange.Offset(0, 21).Value
    STATE.Text = FoundRange.Offset(0, 22).Value
    ZIPCODE.Text = FoundRange.Offset(0, 23).Value
    HOMEPHONE.Text = FoundRange.Offset(0, 24).Value
    MOBILEPHONE.Text = FoundRange.Offset(0, 25).Value
    EC1.Text = FoundRange.Offset(0, 26).Value
    EC2.Text = FoundRange.Offset(0, 27).Value
    EC3.Text = FoundRange.Offset(0, 28).Value
    EC4.Text = FoundRange.Offset(0, 29).Value
    EC5.Text = FoundRange.Offset(0, 30).Value
    EC6.Text = FoundRange.Offset(0, 31).Value
    EC7.Text = FoundRange.Offset(0, 32).Value
    EC8.Text = FoundRange.Offset(0, 33).Value
    EC9.Text = FoundRange.Offset(0, 34).Value
    EC10.Text = FoundRange.Offset(0, 35).Value
    WORKSHEETNOTES.Text = FoundRange.Offset(0, 36).Value
    IRA1.Text = FoundRange.Offset(0, 37).Value
    IRA2.Text = FoundRange.Offset(0, 38).Value
    cboIRA.Value = FoundRange.Offset(0, 39).Value
    IRA4.Text = FoundRange.Offset(0, 40).Value
    CAFHIDate.Text = FoundRange.Offset(0, 41).Value
    CAFHCovType.Text = FoundRange.Offset(0, 42).Value
    CAFHOccCode.Text = FoundRange.Offset(0, 43).Value
    CAFHInsRate.Text = FoundRange.Offset(0, 44).Value
    CAFDentDate.Text = FoundRange.Offset(0, 45).Value
    CAFDentType.Text = FoundRange.Offset(0, 46).Value
    CAFDentRate.Text = FoundRange.Offset(0, 47).Value
    CAFAccDate.Text = FoundRange.Offset(0, 48).Value
    CAFAccRate.Text = FoundRange.Offset(0, 49).Value
    CAFHospDate.Text = FoundRange.Offset(0, 50).Value
    CAFHospRate.Text = FoundRange.Offset(0, 51).Value
    CAFCancerDate.Text = FoundRange.Offset(0, 52).Value
    CAFCancerRate.Text = FoundRange.Offset(0, 53).Value
    CAFCritInsDate.Text = FoundRange.Offset(0, 54).Value
    CAFCritInsRate.Text = FoundRange.Offset(0, 55).Value
    CAFDisInsDate.Text = FoundRange.Offset(0, 56).Value
    CAFDisCovType.Text = FoundRange.Offset(0, 57).Value
    CAFDisInsRate.Text = FoundRange.Offset(0, 58).Value
    CAFLifeDate.Text = FoundRange.Offset(0, 59).Value
    CAFLifeType.Text = FoundRange.Offset(0, 60).Value
    CAFLifeRate.Text = FoundRange.Offset(0, 61).Value
    TERMHealthCX.Text = FoundRange.Offset(0, 62).Value
    TERMDentalCX.Text = FoundRange.Offset(0, 63).Value
    TERMAccCX.Text = FoundRange.Offset(0, 64).Value
    TERMHospCX.Text = FoundRange.Offset(0, 65).Value
    TERMCancerCX.Text = FoundRange.Offset(0, 66).Value
    TERMCritCX.Text = FoundRange.Offset(0, 67).Value
    TERMDisabCX.Text = FoundRange.Offset(0, 68).Value
    TERMLifeCX.Text = FoundRange.Offset(0, 69).Value
    TERMCobraActive.Text = FoundRange.Offset(0, 70).Value
    TERMCobraCX.Text = FoundRange.Offset(0, 71).Value
    End If

    If txtLASTNAME.Text = "" Then

    Set FoundRange1 = Sheets("Empdata").Range("A:A").Find(What:=test1, LookIn:=xlFormulas, LookAt:=xlWhole).Offset(0, 3)

    If FoundRange1 Is Nothing Then
    response = MsgBox("No match for " & Me.EMPNOE.Value, , vbOK)

    If response = vbOK Then
    cmdEMPTY
    End If
    Else
    EMPNOE.Text = FoundRange1.Offset(0, -3).Value
    txtLASTNAME.Text = FoundRange1.Offset(0, 0).Value
    FIRSTNAME.Text = FoundRange1.Offset(0, -2).Value
    MName.Text = FoundRange1.Offset(0, -1).Value
    SOCIALSECNO.Text = FoundRange1.Offset(0, 1).Value
    cboEMP.Value = FoundRange1.Offset(0, 2).Value
    DATEOFHIRE.Text = FoundRange1.Offset(0, 3).Value
    DOT.Text = FoundRange1.Offset(0, 4).Value
    cboSEX.Value = FoundRange1.Offset(0, 5).Value
    POSITION.Text = FoundRange1.Offset(0, 6).Value
    cboWCCLASS.Value = FoundRange1.Offset(0, 7).Value
    cboMARITAL.Value = FoundRange1.Offset(0, 8).Value
    cboPAYROLL.Value = FoundRange1.Offset(0, 9).Value
    STARTPAY.Text = FoundRange1.Offset(0, 10).Value
    CURRENTPAY.Text = FoundRange1.Offset(0, 11).Value
    RAISEDATE.Text = FoundRange1.Offset(0, 12).Value
    RAISETYPE.Text = FoundRange1.Offset(0, 13).Value
    DOB.Text = FoundRange1.Offset(0, 14).Value
    DLSTATE.Text = FoundRange1.Offset(0, 15).Value
    DLNO.Text = FoundRange1.Offset(0, 16).Value
    W2STATUS.Text = FoundRange1.Offset(0, 17).Value
    W2ALLOW.Text = FoundRange1.Offset(0, 18).Value
    W1ADDWH.Text = FoundRange1.Offset(0, 19).Value
    HMADD.Text = FoundRange1.Offset(0, 20).Value
    HOMECITY.Text = FoundRange1.Offset(0, 21).Value
    STATE.Text = FoundRange1.Offset(0, 22).Value
    ZIPCODE.Text = FoundRange1.Offset(0, 23).Value
    HOMEPHONE.Text = FoundRange1.Offset(0, 24).Value
    MOBILEPHONE.Text = FoundRange1.Offset(0, 25).Value
    EC1.Text = FoundRange1.Offset(0, 26).Value
    EC2.Text = FoundRange1.Offset(0, 27).Value
    EC3.Text = FoundRange1.Offset(0, 28).Value
    EC4.Text = FoundRange1.Offset(0, 29).Value
    EC5.Text = FoundRange1.Offset(0, 30).Value
    EC6.Text = FoundRange1.Offset(0, 31).Value
    EC7.Text = FoundRange1.Offset(0, 32).Value
    EC8.Text = FoundRange1.Offset(0, 33).Value
    EC9.Text = FoundRange1.Offset(0, 34).Value
    EC10.Text = FoundRange1.Offset(0, 35).Value
    WORKSHEETNOTES.Text = FoundRange1.Offset(0, 36).Value
    IRA1.Text = FoundRange1.Offset(0, 37).Value
    IRA2.Text = FoundRange1.Offset(0, 38).Value
    cboIRA.Value = FoundRange1.Offset(0, 39).Value
    IRA4.Text = FoundRange1.Offset(0, 40).Value
    CAFHIDate.Text = FoundRange1.Offset(0, 41).Value
    CAFHCovType.Text = FoundRange1.Offset(0, 42).Value
    CAFHOccCode.Text = FoundRange1.Offset(0, 43).Value
    CAFHInsRate.Text = FoundRange1.Offset(0, 44).Value
    CAFDentDate.Text = FoundRange1.Offset(0, 45).Value
    CAFDentType.Text = FoundRange1.Offset(0, 46).Value
    CAFDentRate.Text = FoundRange1.Offset(0, 47).Value
    CAFAccDate.Text = FoundRange1.Offset(0, 48).Value
    CAFAccRate.Text = FoundRange1.Offset(0, 49).Value
    CAFHospDate.Text = FoundRange1.Offset(0, 50).Value
    CAFHospRate.Text = FoundRange1.Offset(0, 51).Value
    CAFCancerDate.Text = FoundRange1.Offset(0, 52).Value
    CAFCancerRate.Text = FoundRange1.Offset(0, 53).Value
    CAFCritInsDate.Text = FoundRange1.Offset(0, 54).Value
    CAFCritInsRate.Text = FoundRange1.Offset(0, 55).Value
    CAFDisInsDate.Text = FoundRange1.Offset(0, 56).Value
    CAFDisCovType.Text = FoundRange1.Offset(0, 57).Value
    CAFDisInsRate.Text = FoundRange1.Offset(0, 58).Value
    CAFLifeDate.Text = FoundRange1.Offset(0, 59).Value
    CAFLifeType.Text = FoundRange1.Offset(0, 60).Value
    CAFLifeRate.Text = FoundRange1.Offset(0, 61).Value
    TERMHealthCX.Text = FoundRange1.Offset(0, 62).Value
    TERMDentalCX.Text = FoundRange1.Offset(0, 63).Value
    TERMAccCX.Text = FoundRange1.Offset(0, 64).Value
    TERMHospCX.Text = FoundRange1.Offset(0, 65).Value
    TERMCancerCX.Text = FoundRange1.Offset(0, 66).Value
    TERMCritCX.Text = FoundRange1.Offset(0, 67).Value
    TERMDisabCX.Text = FoundRange1.Offset(0, 68).Value
    TERMLifeCX.Text = FoundRange1.Offset(0, 69).Value
    TERMCobraActive.Text = FoundRange1.Offset(0, 70).Value
    TERMCobraCX.Text = FoundRange1.Offset(0, 71).Value
    End If
    End If

    End Sub

    Private Sub cmdEMPTY()
    EMPNOE.Text = ""
    txtLASTNAME.Text = ""
    FIRSTNAME.Text = ""
    MName.Text = ""
    SOCIALSECNO.Text = ""
    cboEMP.Value = ""
    DATEOFHIRE.Text = ""
    DOT.Text = ""
    cboSEX.Value = ""
    POSITION.Text = ""
    cboWCCLASS.Value = ""
    cboMARITAL.Value = ""
    cboPAYROLL.Value = ""
    STARTPAY.Text = ""
    CURRENTPAY.Text = ""
    RAISEDATE.Text = ""
    RAISETYPE.Text = ""
    DOB.Text = ""
    DLSTATE.Text = ""
    DLNO.Text = ""
    W2STATUS.Text = ""
    W2ALLOW.Text = ""
    W1ADDWH.Text = ""
    HMADD.Text = ""
    HOMECITY.Text = ""
    STATE.Text = ""
    ZIPCODE.Text = ""
    HOMEPHONE.Text = ""
    MOBILEPHONE.Text = ""
    EC1.Text = ""
    EC2.Text = ""
    EC3.Text = ""
    EC4.Text = ""
    EC5.Text = ""
    EC6.Text = ""
    EC7.Text = ""
    EC8.Text = ""
    EC9.Text = ""
    EC10.Text = ""
    WORKSHEETNOTES.Text = ""
    IRA1.Text = ""
    IRA2.Text = ""
    cboIRA.Value = ""
    IRA4.Text = ""
    CAFHIDate.Text = ""
    CAFHCovType.Text = ""
    CAFHOccCode.Text = ""
    CAFHInsRate.Text = ""
    CAFDentDate.Text = ""
    CAFDentType.Text = ""
    CAFDentRate.Text = ""
    CAFAccDate.Text = ""
    CAFAccRate.Text = ""
    CAFHospDate.Text = ""
    CAFHospRate.Text = ""
    CAFCancerDate.Text = ""
    CAFCancerRate.Text = ""
    CAFCritInsDate.Text = ""
    CAFCritInsRate.Text = ""
    CAFDisInsDate.Text = ""
    CAFDisCovType.Text = ""
    CAFDisInsRate.Text = ""
    CAFLifeDate.Text = ""
    CAFLifeType.Text = ""
    CAFLifeRate.Text = ""
    TERMHealthCX.Text = ""
    TERMDentalCX.Text = ""
    TERMAccCX.Text = ""
    TERMHospCX.Text = ""
    TERMCancerCX.Text = ""
    TERMCritCX.Text = ""
    TERMDisabCX.Text = ""
    TERMLifeCX.Text = ""
    TERMCobraActive.Text = ""
    TERMCobraCX.Text = ""
    txtLASTNAME.SetFocus
    End Sub
    Reply With QuoteRegister To Reply
  4. 04-07-2016, 08:53 AM #4
    Bernie Deitrick
    Bernie Deitrick is offline
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,944

    Re: VBA Userform Multiple Textboxes to Find Record

    Great - glad to hear it.

    Just as an FYI - response should not be a string, but a specific defined type:

    Please Login or Register  to view this content.
    That will enable you to use the autocomplete when you type, say,

    If response =

    After you type the = spacebar, the autocomplete will appear
    Reply With QuoteRegister To Reply
  5. 04-07-2016, 10:06 AM #5
    chamowllie01
    chamowllie01 is offline
    Registered User
    Join Date
    03-23-2016
    Location
    Jacksonville, Florida
    MS-Off Ver
    MS Office 2010
    Posts
    3

    Re: VBA Userform Multiple Textboxes to Find Record

    Great! I will make that change. Thanks again!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Format Multiple Textboxes at once in a Userform
    By Dan... in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-21-2015, 05:34 PM
  2. [SOLVED] Changing multiple textboxes format on a userform
    By Raylou in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-12-2014, 10:11 AM
  3. [SOLVED] Can't find textboxes in userform!
    By karmadog62 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-20-2014, 04:29 PM
  4. J & R Database UserForm error in FIND/DELETE function of MULTIPLE RECORD, vb code fix?
    By srands in forum Excel General
    Replies: 1
    Last Post: 01-03-2013, 01:13 AM
  5. [SOLVED] Populating userform textboxes with specific record/row in excel worksheet
    By danmack in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-19-2012, 05:25 AM
  6. Formatting Multiple Userform Textboxes
    By badeye in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-01-2010, 09:14 AM
  7. [SOLVED] Arrow key operation among multiple TextBoxes in a UserForm
    By George in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-13-2005, 11:06 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1