Userform TextBox not passing value to worksheet when "Add to Sheet" button is selected. Other values are passed

[Derick63]

Hello Everyone.
I'm relatively new to Excel VBA and above average with Excel formulas. A lot of my assistance came from finding answers to my questions from this forum but I've exhausted resources on the web looking for an answer to this issue but to no avail I'm out of resources so I came to the place where I found most of my answers.

I have a very perplexing Userform Control issue I'm in dire need of help with.

One of the TextBox controls, Reg3, in a Userform, is populated with a value using the procedure shown below. It pulls the pay rate from the "Employee Information" worksheet for the Employee Name shown in the ComboBox, Reg2. The procedure works to populate Reg3 TextBox.

Passing the values to the excel worksheet, listed in TextBox1, from Reg1, Reg2 and Reg4 works great but the value in Reg3 will NOT pass to the excel sheet listed in TextBox1.
As you can see in the procedure below I also used Application.VLookup (commented out) to see if VLookup was the issue, it also worked but also didn't pass the value to the sheet listed in TextBox1 either.

Private Sub Reg2_Change()
    'Using this procedure instead of VLookup code below this procedure
    'Neither procedure posts Reg3 to Sheet

    Dim myRange As Range, f As Range

    Set myRange = Worksheets("Employee Information").Range("A:B")
    Set f = myRange.Find(What:=Reg2.Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False) '<--| try and find combobox selected value
    
    If f Is Nothing Then '<--| if not found ...
        Reg3.Value = "" '<--| ... then clear textbox
        Else '<--| ... otherwise...
        Reg3.Value = f.Offset(, 1) '<--| ... fill it with proper value
    End If
   
    'Reg3.Value = Application.VLookup(Reg2.Value, Sheets("Employee Information").Range("B3:I50"), 8, False)
   
End Sub

Code to pass data to the appropriate worksheet; "Add to Sheet" command Button

Private Sub CmdAdd_Click()
    'Button To Add Reg1 through Reg4 Values to Worksheet (sht)
    
    Dim sht 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)

'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 = -1
        For i = 1 To 4
            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
        Next i
    End If

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.Reg2.SetFocus '<-----  focused to select another employee
    End If

End Sub

I have no idea what to do. This is way above my comprehension and I want to understand why this is happening.

I cannot attach the workbook because I do not have post attachments permission.
So you can see the the entire project code for the userform in question here is a link to the file on Google Drive, No log-in necessary. File: EmpEntryIssue.xlsm

Thank you in advance.

Derick

 

[Fluff]

The best way to find the problem, is to remove the error handler.
then set a breakpoint on this line

For i = 1 To 4
            [COLOR=#ff0000]With Me.Controls("Reg" & i)[/COLOR]
'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
        Next i

Once the code stops at that line use F8 to step through the code line by line.
This will show why the Rate value is never getting transferred.

 

[Fluff]

The simplest way to cure the problem is to change this

Private Sub Reg2_Change()

to

Private Sub Reg2_Click()

 

[Derick63]

Fluff,
Thank you so much. Your suggestion worked and I found the error......

[COLOR=#333333]If i > 1 Then .Value = ""

[/COLOR]
should be

If i > 7 Then .Value = ""

I have 7 controls that need values posted to the respective sheet.

I hope this helps others as well. if not for debugging then for populating a userform textbox and posting to a sheet.

Thanks a million Fluff..... My new hero.

 

[Fluff]

Glad to help & thanks for the feedback

 

[Derick63]

When I need help I know where to go now. Right here!

Oh, thanks also for the prompt replies.