Data Entry User Form

[Plukey]

Currently I'm using a user form to add data to a Specific sheet in my workbook. Ive been tasked to change the way the data is entered. It now needs to transfer to a specific week tab 1-52 …Fiscal year
For an example 5 text boxes *Date* *Category* *total hrs* *New* *Completed* after all boxes are filled click enter and data transfer to specific tab that matches the date, but in a fiscal year.

Code below currently transfer to one sheet with only 4 fields, I need to add the date textbox or a calendar. I feel it could be easier, but this is what they want. I'm open for suggestions and all help is appreciated!

Dim Lastrow As Long, ws As Worksheet

    Set ws = Sheets("WorkOrder") ' this needs to be changed to look for week that's between the date given

    Lastrow = ws.Range("B" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row
     ws.Range("B" & Lastrow).Value = Reg1.Text  'Adds the TextBox3 into Col A & Last Blank Row
ws.Range("C" & Lastrow).Value = Reg2.Text
ws.Range("D" & Lastrow).Value = Reg3.Text
ws.Range("E" & Lastrow).Value = Reg4.Text

Me.Reg1.Value = ""
Me.Reg2.Value = ""
Me.Reg3.Value = ""
Me.Reg4.Value = ""
    Unload Me

I also have a formula that tells me what week number the date is, not sure if that will help...

=IF(((WEEKNUM(B5)-WEEKNUM(DATE(2020,7,1)))+1)<0,(WEEKNUM(DATE(2020,7,1))+WEEKNUM(B5))-1, (WEEKNUM(B5)-WEEKNUM(DATE(2020,7,1)))+1)

Thank You!

 

[Plukey]

I've worked up this code so far & I can specify what sheet but need it to look up what week from the date. the tabs are listed Week1,week2,week3 and etc...
Also it throws a error if textbox1 is empty ...is there a way to just msg if any of the boxes are empty instead of trying to debug? this code is placed in a userform named Workorder

Private Sub CommandButton1_Click()

    With Worksheets(TextBox1.Value).Range("A65536").End(xlUp)



.Offset(1, 0) = Workorder.ComboBox1.Value
.Offset(1, 1) = Workorder.TextBox3.Value
.Offset(1, 2) = Workorder.TextBox4.Value
.Offset(1, 3) = Workorder.TextBox5.Value
  

  
Me.TextBox1.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Unload Me
End With
End Sub

 

[dmt32]

Hi,
try this update to your code & see if helps

Private Sub CommandButton1_Click()
    Dim Lastrow         As Long
    Dim wsName          As String
   
    wsName = Me.TextBox1.Value
    If Len(wsName) = 0 Then Exit Sub
   
    If Evaluate("ISREF('" & wsName & "'!A1)") Then
        With Worksheets(wsName)
            'Finds next blank row in range
            Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
            .Cells(Lastrow, 1).Value = Me.ComboBox1.Value
            .Cells(Lastrow, 2).Value = Me.TextBox3.Value
            .Cells(Lastrow, 3).Value = Me.TextBox4.Value
            .Cells(Lastrow, 4).Value = Me.TextBox5.Value
        End With
        Unload Me
    Else
        MsgBox wsName & Chr(10) & "Worksheet Not Found", 48, "Not Found"
    End If
End Sub

Dave

 

[Plukey]

Outstanding Your 'e Awesome!

 

[dmt32]

Outstanding Your 'e Awesome!

Glad update dies what you wanted & many thanks for your very generous feedback - very much appreciated

Dave