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

Derick63

New Member
Joined
Apr 6, 2018
Messages
40
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.

Code:
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
Code:
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,127
Office Version
  1. 365
Platform
  1. Windows
The best way to find the problem, is to remove the error handler.
then set a breakpoint on this line
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,127
Office Version
  1. 365
Platform
  1. Windows
The simplest way to cure the problem is to change this
Code:
Private Sub Reg2_Change()
to
Code:
Private Sub Reg2_Click()
 

Derick63

New Member
Joined
Apr 6, 2018
Messages
40
Fluff,
Thank you so much. Your suggestion worked and I found the error......
Code:
[COLOR=#333333]If i > 1 Then .Value = ""
[/COLOR]
should be
Rich (BB code):
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,127
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

Derick63

New Member
Joined
Apr 6, 2018
Messages
40
When I need help I know where to go now. Right here!

Oh, thanks also for the prompt replies.
 

Forum statistics

Threads
1,144,676
Messages
5,725,723
Members
422,636
Latest member
GatorsBucs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top