Sending Userform data to a specific worksheet

[suarezri]

I have an existing workbook created to track/monitor customer service reports for approximately 90 employees, I'm trying to add a userform so that supervisors don't need to constantly change to the employees sheet to enter the data, I'd like the userform to do it for them.

I have the userform created and coded, it works just fine if I want it to go to a single sheet, but I'd like it to go to the correct employee's sheet. I know I'm over complicating this (I'm a beginner) but I don' know where/how to make it simpler. I'm using a combo box with all of the names.

Private Sub UserForm_Initialize()
'Fill CmboxEmployee
With EmployeeComboBox
.AddItem "Smith John"
.AddItem "Smith John2"

.AddItem "Smith John3"
.AddItem "Smith John4"
.AddItem "Smith John5"
.AddItem "Smith John6"
.AddItem "Smith John7"
.AddItem "Smith John8"
.AddItem "Smith John9"
.AddItem "Smith John10"
End With
End Sub

Private Sub CMDSubmit_Click()
Dim emptyRow As Long
'Make Employee Sheet active
If EmployeeComboBox.Value = "Smith, John" Then
ActiveWorkbook.Worksheets("Smith, John").Select
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
With Worksheet
.Cells(emptyRow, 2).Value = DTPicker1.Value
.Cells(emptyRow, 3).Value = AquaTextBox.Value
.Cells(emptyRow, 4).Value = CFSTextBox.Value
.Cells(emptyRow, 5).Value = NotesTextBox.Value
End With
End If
EmployeeComboBox.Value = ""

DTPicker1.Value = ""
AquaTextBox.Value = ""
CFSTextBox.Value = ""
NotesTextBox.Value = ""
End Sub

Private Sub ClearForm_Click()

Call UserForm_Initialize
End Sub

Private Sub CloseButton_Click()
Unload Me
End Sub

Thanks!

 

[dmt32]

Hi welcome to the board.

Firstly, try not to hard code the employee names - it easier and more flexible to place them in separate worksheet (which you can hide) & have your form read the names from it.

See if these changes to you code help you:

Private Sub EmployeeComboBox_Change()
Me.CMDSubmit.Enabled = Me.EmployeeComboBox.ListIndex <> -1
End Sub


Private Sub UserForm_Initialize()
    Dim EmployeeNames As Variant


    With ThisWorkbook.Worksheets("Employees")
        EmployeeNames = .Range(.Range("A1"), .Range("A" & .Rows.Count).End(xlUp)).Value
    End With


    'Fill CmboxEmployee
    With Me.EmployeeComboBox
        .RowSource = ""
        .Clear
        .List = EmployeeNames
    End With
    
    Me.CMDSubmit.Enabled = False


End Sub


Private Sub CMDSubmit_Click()
    Dim emptyRow As Long
    'Make Employee Sheet active
    On Error GoTo myerror
    With ThisWorkbook.Worksheets(Me.EmployeeComboBox.Text)
         .Activate
        emptyRow = WorksheetFunction.CountA(.Range("A:A")) + 1
        .Cells(emptyRow, 2).Value = DTPicker1.Value
        .Cells(emptyRow, 3).Value = AquaTextBox.Value
        .Cells(emptyRow, 4).Value = CFSTextBox.Value
        .Cells(emptyRow, 5).Value = NotesTextBox.Value
    End With




Call ClearForm_Click


myerror:
If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub


Private Sub ClearForm_Click()
    Dim ctrl As Control
    For Each ctrl In Me.Controls
        Select Case TypeName(ctrl)
        Case "TextBox"
            ctrl.Text = " "
        Case "ComboBox"
            ctrl.ListIndex = -1
        End Select
    Next ctrl
End Sub


Private Sub CloseButton_Click()
    Unload Me
End Sub

You will need to add a new worksheet named "Employees" and add all the names in Column A - when completed you can hide the sheet

This is not tested but code assumes that sheet names match employee names.

Hope Helpful

Dave

 

[suarezri]

Hi welcome to the board.

Firstly, try not to hard code the employee names - it easier and more flexible to place them in separate worksheet (which you can hide) & have your form read the names from it.

See if these changes to you code help you:

Private Sub EmployeeComboBox_Change()
Me.CMDSubmit.Enabled = Me.EmployeeComboBox.ListIndex <> -1
End Sub


Private Sub UserForm_Initialize()
    Dim EmployeeNames As Variant


    With ThisWorkbook.Worksheets("Employees")
        EmployeeNames = .Range(.Range("A1"), .Range("A" & .Rows.Count).End(xlUp)).Value
    End With


    'Fill CmboxEmployee
    With Me.EmployeeComboBox
        .RowSource = ""
        .Clear
        .List = EmployeeNames
    End With
    
    Me.CMDSubmit.Enabled = False


End Sub


Private Sub CMDSubmit_Click()
    Dim emptyRow As Long
    'Make Employee Sheet active
    On Error GoTo myerror
    With ThisWorkbook.Worksheets(Me.EmployeeComboBox.Text)
         .Activate
        emptyRow = WorksheetFunction.CountA(.Range("A:A")) + 1
        .Cells(emptyRow, 2).Value = DTPicker1.Value
        .Cells(emptyRow, 3).Value = AquaTextBox.Value
        .Cells(emptyRow, 4).Value = CFSTextBox.Value
        .Cells(emptyRow, 5).Value = NotesTextBox.Value
    End With




Call ClearForm_Click


myerror:
If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub


Private Sub ClearForm_Click()
    Dim ctrl As Control
    For Each ctrl In Me.Controls
        Select Case TypeName(ctrl)
        Case "TextBox"
            ctrl.Text = " "
        Case "ComboBox"
            ctrl.ListIndex = -1
        End Select
    Next ctrl
End Sub


Private Sub CloseButton_Click()
    Unload Me
End Sub

You will need to add a new worksheet named "Employees" and add all the names in Column A - when completed you can hide the sheet

This is not tested but code assumes that sheet names match employee names.

Hope Helpful

Dave

It works as I have envisioned!!! But if I may ask for some more help; it erases the submitted data and replaces it with each submission, is it possible to have it find the next row and submit the data so I have all of the submitted data?

 

[suarezri]

It works as I have envisioned!!! But if I may ask for some more help; it erases the submitted data and replaces it with each submission, is it possible to have it find the next row and submit the data so I have all of the submitted data?

Disregard this, I had a blank column that affected this; problem solved! Thank you so much for your help.

One final question, after submitting it takes me to the specific worksheet, is there a way to prevent/stop this so that it just stays as the sheet that I have the command button on to initialize the userform?

 

[dmt32]

Hi,
In you CMDSubmit procedure, Just Delete this line:

.Activate

I only included it as thought it was what you wanted.

Dave

 

[suarezri]

I'm still trying to figure out what I need as I go. I have a vision but I know there are limits to it sometimes! Thank you for your help and patience.