Userform Textbox Vlookup works but not passing to Excel sheet

[Derick Pitcher]

Hello All,
This is my first post but I've been all over Chandoo.org for quite some time and the information here has helped me tremendously. I'm also using some code from Chandoo.org in my project.
I am relatively new to VBA and pretty good at Excel formulas (need to learn a lot more though). I need to take a VBA class.....

This issue has been a major pain for me. I'm so close to completion but this is keeping me back.
I built a payroll project and use a Userform (EmpEnt) for employee entries. I have the following controls in EmpEnt:

• Reg1 - DTPicker - User choses a date ***Posted To Sheet***
• Textbox1 - TextBox - Month value is displayed from Reg1. This is the name of the month payroll sheet posting all entered data in EmpEnt.
• Reg2 - ComboBox - user selects an Employee's name ***Posted To Sheet***
• Reg3 - TextBox - Hourly Pay Rate is Automatically filled in via Vlookup using Reg2 Change ***Not Posted To Sheet***
• Reg4 - TextBox - User enters employee's hours for the week worked ***Posted To Sheet***
• Reg5 - TextBox - User enters employee's hours for Public Holiday & Day Off worked ***Posted To Sheet***
• Reg6 - TextBox - User enters employee's Insurance Deductions ***Posted To Sheet***
• Reg7 - TextBox - User enters employee's Miscellaneous Deductions ***Posted To Sheet***
  
• CmdAdd - Button - Sends the entered data in Reg1 through Reg7 to the sheet listed in TextBox1 ***Need Help*** Sends all but Reg3
  
• CmdClr - Button - Clears the entered data ***Need Help***
• CmdExit - Button - Copies all data with date in Reg1 from sheet in TextBox1 to Payroll sheet for use in word Mail Merge Pay Slips then closes the userform EmpEnt

I do apologise for the lengthy post and I really hope that I was clear

Here's a link to the file on Google Drive, no log-in necessary.
https://drive.google.com/file/d/13cQs0WvVz2m8ezPeEkLTGhviiXrGe2xH/view?usp=sharing

And now, the code where I'm having the problems.....
Code for EmpEnt Userform. Procedures with problems in red text.

Option Explicit

Private Sub CmdAdd_Click() 'not passing Reg3 to worksheet
    'Button To Add Reg1 through Reg7 Values to Worksheet (sht)
   
    Dim sht As Worksheet
    'Dim pydt As Worksheet
    Dim nextrow As Range
    Dim i As Integer, c As Integer
   
'turn error handling on
    On Error GoTo myerror
   
'set the variable for the sheets
    Set sht = ThisWorkbook.Worksheets(TextBox1.Value)
    'Set pydt = ThisWorkbook.Worksheets(TextBox1.Value)

'unprotect sheet posting to
    sht.Unprotect Password:=""
   
'check for Employee name
    If Trim(Me.Reg2.Value) = "" Then
        Me.Reg2.SetFocus
        MsgBox "Please select an Employee", 48, "Entry Required"
    Else
   
'next blank row
        Set nextrow = sht.Cells(sht.Rows.Count, 2).End(xlUp).Offset(1, 0)
        c = 0
        For i = 1 To 7
            With Me.Controls("Reg" & i)
'add the data to the selected worksheet
                nextrow.Offset(, c).Value = .Value
'clear the values in the userform
               If i > 1 Then .Value = ""
            End With
'next column
            c = c + 1
'move to column
            If c = 5 Then c = c + 4
        Next i
    End If

'protect sheet posting to
    sht.Protect Password:=""
   
myerror:
        If Err <> 0 Then
'something went wrong
            MsgBox (Error(Err)), 48, "Error"
        Else
           
'communicate the results
            MsgBox "The values have been sent to the " & sht.Name & " sheet", 64, "Record Saved"
            Me.Reg1.SetFocus
    End If

End Sub


Private Sub CmdClr_Click() 'Compile Error: Variable not defined

For Each ctrl In Me.Controls

    Select Case TypeName(ctrl)
        Case "TextBox"
            ctrl.Text = ""
        Case "ComboBox"
            ctrl.ListIndex = -1
   
    End Select

Next

End Sub


Private Sub CmdExit_Click()
    Dim ws As Worksheet
    Dim pyrlsh As Worksheet
   
'set the variable for the sheets
    Set ws = ThisWorkbook.Worksheets(EmpEnt.TextBox1.Value)
    Set pyrlsh = ThisWorkbook.Worksheets("PayRoll")
   
'unprotect sheet posting to
    ws.Unprotect Password:=""
    pyrlsh.Unprotect Password:=""
   
'copy date and month to payroll sheet
    [PayDate] = Reg1.Value
    [PostSht] = TextBox1.Value
   
'macro to clear the sheet of all data and border formatting
    Call ClrPyRl
   
'protect sheet posting to
    ws.Protect Password:=""
    pyrlsh.Protect Password:=""

    ThisWorkbook.Worksheets("Interface").Activate
   
    Unload Me
   
End Sub


Private Sub CmdExitToNIS_Click() 'Exits and activates NIS Worksheet
    Dim ws As Worksheet
    Dim pyrlsh As Worksheet
   
'set the variable for the sheets
    Set ws = ThisWorkbook.Worksheets(EmpEnt.TextBox1.Value)
    Set pyrlsh = ThisWorkbook.Worksheets("PayRoll")
   
'unprotect sheet posting to
    ws.Unprotect Password:=""
    pyrlsh.Unprotect Password:=""
   
'copy date and month to payroll sheet
    [PayDate] = Reg1.Value
    [PostSht] = TextBox1.Value
   
    Call ClrPyRl
   
'protect sheet posting to
    ws.Protect Password:=""
    pyrlsh.Protect Password:=""

    ThisWorkbook.Worksheets("NI 184").Activate
   
    Unload Me

End Sub


Private Sub Reg1_Change()
    Dim DT As Date
   
    DT = DateValue(Me.Reg1.Value)

    TextBox1.Value = Format(DT - 4, "mmmm")
   
End Sub


Private Sub Reg2_Change() 'VLookup procedure - need better error checking
    'Dim EmpName As String
    'Dim empinf As Worksheet
   
    'EmpName = Me.Reg2.Value
    'Set empinf = ThisWorkbook.Worksheets("Employee Information")
   
    'empinf.Unprotect Password:=""
    'On Error Resume Next
    Reg3.Value = Application.VLookup(Reg2.Value, Sheets("Employee Information").Range("B3:I50"), 8, False)
    'On Error GoTo 0
   
    'If Ret <> "" Then MsgBox Ret
   
    'empinf.Protect Password:=""
   
End Sub


Private Sub TextBox1_Change()
    'EmpEnt.TextBox1.Value.Select
   
    Me.MonthView1.Value = Me.Reg1.Value
       
End Sub


Private Sub UserForm_Initialize()
    Me.Reg1.Value = Date

End Sub

 

[Derick Pitcher]

I think I need to add this explanation.....
The Reg3 VLookup TextBox is pulling the hourly rate pass it into the month sheet. I did not want to put a live VLookup directly in the column cell for reasons of an increase in the employee's hourly rate. This way all previous rates remain the same as entered on that date.

 

[Derick Pitcher]

SOLVED!!!!!

If i > 1 Then .Value = ""

should be

If i > 7 Then .Value = ""

I have 7 values to be passed into the respective worksheet

 

[Derick Pitcher]

I removed the file and link and replaced it with an easier to understand example for those who want to learn about userform controls, getting values into a textbox on a userform from a worksheet then passing those values to a specific worksheet in a multiple worksheet workbook.