multiple vba to worksheet based on criteria

[wbfd25]

thank you to anyone who can help i am making a userform to input employees times. i plan to have a worksheet for each employe i would the userform to copy all data from the right text boxes and send them to the right sheet based on the name. each time this happens it needs to go into a new row.

Private Sub CommandButton1_Click()
Dim cNum As Integer
Dim X As Integer
Dim nextrow As Range
Dim sht As String
'set the variable for the sheet
sht = con1.Value
sht = con2.Value
sht = con3.Value
sht = con4.Value
sht = con5.Value
'Check for values
If Me.ComboBox1.Value = "" Then
MsgBox "Please select a person who is filing out the form"
Exit Sub
End If
'change the number for the number of controls on the userform
cNum = 5
'add the data to the selected worksheet
Set nextrow = Sheets(sht).Cells(Rows.Count, 6).End(xlUp).Offset(1, 0)
For X = 1 To cNum
nextrow = Me.Controls("Reg" & X).Value
Set nextrow = nextrow.Offset(0, 1)
Next
'clear the Values in the userform
For X = 1 To cNum
Me.Controls("Reg""con" & X).Value = ""
Next
'communicate the results
MsgBox "Thank you"

hopefully this makes sense

 

[wbfd25]

when this is ran i get an error 9 i belive (Set nextrow = Sheets(sht).Cells(Rows.Count, 6).End(xlUp).Offset(1, 0)) based off of this code

 

[L. Howard]

Hi wbfd25,

This is not a userform, but seems to work for what I think you are doing.
Probably way too many entry fields for your application, but that is easy enough to change.
All the 12 entry lists can be change to meet your sheet usage items as well as the number of them. May not need twelve as an example, or more than twelve.
You would change to "Time In", "Time Out" "Break Time" "Lunch" etc. etc.

In a test workbook with this code in a standard module, and a few sheets named with employee names, do this:

Call the macro from any sheet, enter a bogus number in the window, i.e. 12345, with a comma as delimiter, this will satisfy the Employee ID number, and then enter the employee's name (same as a sheet name) and comma delimiter, then any of the hour values you would normally enter, each delimited with a comma. Click OK

(you can skip an entry by , , which is ,<space>, )

The data you entered in the input box will go to the sheet named for the employee (second data entry).

Howard

Option Explicit

Sub Inputbox_Comma_Delimit()
Dim Empl_Info, i As Long
Dim myArr As Variant
Dim Dest As Range


Empl_Info = Application.InputBox(prompt:="Use a comma ( , ) as Delimiter" & vbCr & vbCr & _
            "Example - 12345,Name,Type etc." & vbCr & _
            "and a SPACE to skip an entry." & vbCr & vbCr & _
            "1 - Employee ID" & vbCr & _
            "2 - Name" & vbCr & _
            "3 - Title " & vbCr & _
            "5 - M/F Gender" & vbCr & _
            "6 - Contact" & vbCr & _
            "7 - Division" & vbCr & _
            "8 - Deptartment" & vbCr & _
            "9 - Section" & vbCr & _
            "10 - Supervisor" & vbCr & _
            "11 - Crew" & vbCr & _
            "12 - Role Description" & vbCr, _
            Title:="Employee Information New Entry", Type:=2)
            
If Len(Empl_Info) = 0 Or Empl_Info = False Then

    MsgBox "No Entry"
    Exit Sub

End If

myArr = Split(Empl_Info, ",")

With Sheets(myArr(1))

    Set Dest = .Cells(Rows.Count, 1).End(xlUp)(2)
    For i = LBound(myArr) To UBound(myArr)
        If IsNumeric(myArr(i)) Then
            Dest.Offset(, i) = CLng(myArr(i)) '/ changes number as text output to numeric
        Else
            Dest.Offset(, i) = myArr(i)
        End If
    Next
End With
            
End Sub

 

[wbfd25]

Hi wbfd25,

This is not a userform, but seems to work for what I think you are doing.
Probably way too many entry fields for your application, but that is easy enough to change.
All the 12 entry lists can be change to meet your sheet usage items as well as the number of them. May not need twelve as an example, or more than twelve.
You would change to "Time In", "Time Out" "Break Time" "Lunch" etc. etc.

In a test workbook with this code in a standard module, and a few sheets named with employee names, do this:

Call the macro from any sheet, enter a bogus number in the window, i.e. 12345, with a comma as delimiter, this will satisfy the Employee ID number, and then enter the employee's name (same as a sheet name) and comma delimiter, then any of the hour values you would normally enter, each delimited with a comma. Click OK

(you can skip an entry by , , which is ,<space>, )

The data you entered in the input box will go to the sheet named for the employee (second data entry).

Howard

Option Explicit

Sub Inputbox_Comma_Delimit()
Dim Empl_Info, i As Long
Dim myArr As Variant
Dim Dest As Range


Empl_Info = Application.InputBox(prompt:="Use a comma ( , ) as Delimiter" & vbCr & vbCr & _
            "Example - 12345,Name,Type etc." & vbCr & _
            "and a SPACE to skip an entry." & vbCr & vbCr & _
            "1 - Employee ID" & vbCr & _
            "2 - Name" & vbCr & _
            "3 - Title " & vbCr & _
            "5 - M/F Gender" & vbCr & _
            "6 - Contact" & vbCr & _
            "7 - Division" & vbCr & _
            "8 - Deptartment" & vbCr & _
            "9 - Section" & vbCr & _
            "10 - Supervisor" & vbCr & _
            "11 - Crew" & vbCr & _
            "12 - Role Description" & vbCr, _
            Title:="Employee Information New Entry", Type:=2)
            
If Len(Empl_Info) = 0 Or Empl_Info = False Then

    MsgBox "No Entry"
    Exit Sub

End If

myArr = Split(Empl_Info, ",")

With Sheets(myArr(1))

    Set Dest = .Cells(Rows.Count, 1).End(xlUp)(2)
    For i = LBound(myArr) To UBound(myArr)
        If IsNumeric(myArr(i)) Then
            Dest.Offset(, i) = CLng(myArr(i)) '/ changes number as text output to numeric
        Else
            Dest.Offset(, i) = myArr(i)
        End If
    Next
End With
            
End Sub

thank you for the response will this work for multiple entries in on form? i work for a fire department and im tring to do sometype of hours recording system. each morning the shift capt. is to fill this out. there will be atleast 4 guys at one time. i have a text box with the name of the firefighter, wether or not they have capt. pay, hours, overtime hours, reason for ot, and person being replaced. i also have a second tab with a call back list with names, and hours and why they came back. i still want to sort all info by name and place correct info in the right spread sheet. sorry i didnt explain this earlier i could find the words i wish i could send a screen shot but idk how. thank you agian very much for the help.</space>

 

[L. Howard]

Have a go with this test workbook.

https://www.dropbox.com/s/k5bzk3d22d55nv7/InPut Box Comma Delim Fire House test Drop Box.xlsm?dl=0

Click the blue shape, and make a entry as per example on sheet Form


We can adjust as needed, I'm sure.

Howard

The code assigned to the blue shape:

Option Explicit

Sub Inputbox_Comma_Err()
Dim Empl_Info, i As Long
Dim myArr As Variant
Dim Dest As Range

Empl_Info = InputBox("First entry is - ""Employee Name""" & vbCr & _
                     "Then enter employee..." & vbCr & _
                     "    Info1 Info2 etc." & vbCr & _
                     "Each separated by a comma.")

   If Len(Empl_Info) = 0 Then
       MsgBox "No Entry"
       Exit Sub
   End If

myArr = Split(Empl_Info, ",")

With Sheets(myArr(0))

    Set Dest = .Cells(Rows.Count, 1).End(xlUp)(2)
    For i = LBound(myArr) To UBound(myArr)
        If IsNumeric(myArr(i)) Then
            Dest.Offset(, i) = CLng(myArr(i)) '/ changes number as text output to numeric
        Else
            Dest.Offset(, i) = myArr(i)
        End If
    Next
End With
            
End Sub