Data Entry User Form

Plukey

Board Regular
Joined
Apr 19, 2019
Messages
61
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!

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

SQL:
=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!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Plukey

Board Regular
Joined
Apr 19, 2019
Messages
61
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

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

Well-known Member
Joined
Jul 3, 2012
Messages
6,508
Office Version
  1. 2019
Platform
  1. Windows
Hi,
try this update to your code & see if helps

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

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,508
Office Version
  1. 2019
Platform
  1. Windows
Outstanding Your 'e Awesome!

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

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,129,383
Messages
5,635,942
Members
416,889
Latest member
dhegs

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