Help Please

[AndrewGKenneth]

Hi there,
Could anyone help me please? I have created two working user forms and need to get them to sync in the worksheet. (Userform1) will be used to enter data at the start of the working day and the second user form (Userform2) will be used to complete the data at the end of the working day.

Userform1 will populate the following columns on the worksheet: 1,2,4,5,6,7,8,9
And Userform2 columns: 3,10,11,12

Both userforms will have a job number for the user to input that can link the user forms. However, the problem I am having is that there will not only be one job number being worked on each day.

For example, Job Number – J124021 starts at 11pm and the user will fill in Userform1 to let us know this has started.
A different job, Job Number – J172831 then finishes at 8pm that day so the user will fill in Userform2 to let us know this job has finished for the day. The problem is that when this information is completed it is currently going into the next available blank columns 3,10,11 and 12 – which in this case would be next to the previous data inputted from userform1 (first job – job number J124021) and therefore would be the wrong information.

Therefore, what I need to do is create a code in userform2 (I imagine using the vlookup function) to recognize the same job number in column 1 of the worksheet (as both userforms can be linked by job numbers) and only complete columns 3,10,11,12 if the job number in column 1 matches. I Imagine this would be using the Command Button add.

For your information the relevant textboxes are labelled as follows:
Userform 2 – Data to be inputted into Column 3 – TextBox_End
Data to be inputted into Column 10 – TextBox_FG
Data to be inputted into Column 11 – TextBox_NG
Data to be inputted into Column 12 – TextBox_MAT_NG
The vlookup will be looking in Column A of Sheet1 (i.e. Job Number) and this is TextBox_JobNumber in both userforms.

Please let me know if you need any more info and if anyone can provide me with code to perform this function I would be very grateful. I should also mention that there will be multiple entries for each job number, so the code will need to find the latest matching job number and only fill the columns if the values are blank in columns 3,10,11 and 12.

Thanks!
Andrew

 

[mse330]

Hi Andrew,

Check the below code & change it to your needs. It will not validate if data is entered in other columns but it will return the last row number of that particular job you are looking for in UserForm2. In the below example, I am assuming the job number is in column A

Sub FindRowNumber()
Dim Code As String, lRow As Long, Job_Row As Range
Code = InputBox("Input Code Please")
lRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Set Job_Row = Range("A1:A" & lRow).Find(What:=Code, SearchDirection:=xlPrevious)
If Job_Row Is Nothing Then
    MsgBox "No match found !"
Else
    MsgBox "Row # " & Job_Row.Row
End If
End Sub

 

[AndrewGKenneth]

Thanks MSE! I will give that a try tomorrow. As I am relatively knew to VBA would that go in the code as part of the userform under my command add button or as a macro on the active sheet ?

 

[mse330]

This will not work right away, I was just giving you an example. If you post the code you're currently using in UserForm2, we can add these lines to it along with the required changes

 

[AndrewGKenneth]

Ok thanks mse I’ll try incorporate it into my code tomorrow, if it doesn’t work I’ll post my code on here. Thanks again, it is much appreciated !

 

[AndrewGKenneth]

Hi MSE,

As promised here is my full code for my Userform2. I tried to incorporate your code but must have done it wrong any advice would be much appreciated.

Private Sub CommandButton_Add_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
'Check for Customer Number
If Trim(Me.TextBox_LotNumber.Value) = "" Or Me.TextBox_JobNumber.Value = "" Or Me.TextBox_LotNumber.Value = "" Or Me.TextBox_FG.Value = "" Then
Me.TextBox_JobNumber.SetFocus
MsgBox "Please complete all fields"
Exit Sub
End If
'Find first empty row in database
EmptyRow = WorksheetFunction.CountA(Range("B:B")) + 1
Cells(EmptyRow, 1).Value = TextBox_JobNumber.Value
Cells(EmptyRow, 2).Value = TextBox_End.Value
Cells(EmptyRow, 4).Value = TextBox_LotNumber.Value
Cells(EmptyRow, 5).Value = TextBox_ProductNumber.Value
Cells(EmptyRow, 6).Value = TextBox_PartName.Value
Cells(EmptyRow, 7).Value = TextBox_DrawingNumber.Value
Cells(EmptyRow, 8).Value = Textbox_Customer.Value
Cells(EmptyRow, 9).Value = TextBox_Order.Value
Cells(EmptyRow, 10).Value = TextBox_FG.Value
Cells(EmptyRow, 11).Value = TextBox_NG.Value
Cells(EmptyRow, 12).Value = TextBox_MAT_NG.Value
MsgBox "Data Added", vbOKOnly + vbInformation, "Data Added"
'clear the data
TextBox_JobNumber.Value = ""
TextBox_End.Value = ""
TextBox_LotNumber.Value = ""
TextBox_ProductNumber.Value = ""
TextBox_PartName.Value = ""
TextBox_DrawingNumber.Value = ""
Textbox_Customer.Value = ""
TextBox_Order.Value = ""
TextBox_FG.Value = ""
TextBox_NG.Value = ""
TextBox_MAT_NG.Value = ""
TextBox_Remaining.Value = ""
End Sub
Private Sub CommandButton_Cancel_Click()
TextBox_JobNumber.Value = ""
TextBox_End.Value = ""
TextBox_LotNumber.Value = ""
TextBox_ProductNumber.Value = ""
TextBox_PartName.Value = ""
TextBox_DrawingNumber.Value = ""
Textbox_Customer.Value = ""
TextBox_Order.Value = ""
TextBox_FG.Value = ""
TextBox_NG.Value = ""
TextBox_MAT_NG.Value = ""
TextBox_Remaining.Value = ""
End Sub
Private Sub CommandButton_Close_Click()
Unload Me
End Sub
Private Sub TextBox_FG_AfterUpdate()
With Sheet2
.Range("Z2").Value = Me.TextBox_JobNumber.Value
End With
With Me
.TextBox_Remaining.Value = Sheet2.Range("AB3").Value - Val(TextBox_FG)
End With
End Sub

Private Sub TextBox_JobNumber_AfterUpdate()
'Check to see if value exists
If WorksheetFunction.CountIf(Sheet1.Range("A:A"), Me.TextBox_JobNumber) = 0 Then
MsgBox "Please enter valid Job Number"
Me.TextBox_JobNumber = ""
Exit Sub
End If

'Lookup based on first control
With Me
.TextBox_LotNumber = Application.WorksheetFunction.VLookup((Me.TextBox_JobNumber), Sheet1.Range("Lookup"), 4, 0)
.TextBox_ProductNumber = Application.WorksheetFunction.VLookup((Me.TextBox_JobNumber), Sheet1.Range("Lookup"), 5, 0)
.TextBox_PartName = Application.WorksheetFunction.VLookup((Me.TextBox_JobNumber), Sheet1.Range("Lookup"), 6, 0)
.TextBox_DrawingNumber = Application.WorksheetFunction.VLookup((Me.TextBox_JobNumber), Sheet1.Range("Lookup"), 7, 0)
.Textbox_Customer = Application.WorksheetFunction.VLookup((Me.TextBox_JobNumber), Sheet1.Range("Lookup"), 8, 0)
.TextBox_Order = Application.WorksheetFunction.VLookup((Me.TextBox_JobNumber), Sheet1.Range("Lookup"), 9, 0)
End With
With Me
TextBox_End.Text = Now()
End With
End Sub

 

[AndrewGKenneth]

Apologies, I meant to post this code: Please refer to this one.

Private Sub CommandButton_Add_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
'Check for Customer Number
If Trim(Me.TextBox_LotNumber.Value) = "" Or Me.TextBox_JobNumber.Value = "" Or Me.TextBox_LotNumber.Value = "" Or Me.TextBox_FG.Value = "" Then
Me.TextBox_JobNumber.SetFocus
MsgBox "Please complete all fields"
Exit Sub
End If
'Find first empty row in database
EmptyRow = WorksheetFunction.CountA(Range("B:B")) + 1
Cells(EmptyRow, 1).Value = TextBox_JobNumber.Value
Cells(EmptyRow, 2).Value = TextBox_End.Value
Cells(EmptyRow, 10).Value = TextBox_FG.Value
Cells(EmptyRow, 11).Value = TextBox_NG.Value
Cells(EmptyRow, 12).Value = TextBox_MAT_NG.Value
MsgBox "Data Added", vbOKOnly + vbInformation, "Data Added"
'clear the data
TextBox_JobNumber.Value = ""
TextBox_End.Value = ""
TextBox_LotNumber.Value = ""
TextBox_ProductNumber.Value = ""
TextBox_PartName.Value = ""
TextBox_DrawingNumber.Value = ""
Textbox_Customer.Value = ""
TextBox_Order.Value = ""
TextBox_FG.Value = ""
TextBox_NG.Value = ""
TextBox_MAT_NG.Value = ""
TextBox_Remaining.Value = ""
End Sub
Private Sub CommandButton_Cancel_Click()
TextBox_JobNumber.Value = ""
TextBox_End.Value = ""
TextBox_LotNumber.Value = ""
TextBox_ProductNumber.Value = ""
TextBox_PartName.Value = ""
TextBox_DrawingNumber.Value = ""
Textbox_Customer.Value = ""
TextBox_Order.Value = ""
TextBox_FG.Value = ""
TextBox_NG.Value = ""
TextBox_MAT_NG.Value = ""
TextBox_Remaining.Value = ""
End Sub
Private Sub CommandButton_Close_Click()
Unload Me
End Sub
Private Sub TextBox_FG_AfterUpdate()
With Sheet2
.Range("Z2").Value = Me.TextBox_JobNumber.Value
End With
With Me
.TextBox_Remaining.Value = Sheet2.Range("AB3").Value - Val(TextBox_FG)
End With
End Sub

Private Sub TextBox_JobNumber_AfterUpdate()
'Check to see if value exists
If WorksheetFunction.CountIf(Sheet1.Range("A:A"), Me.TextBox_JobNumber) = 0 Then
MsgBox "Please enter valid Job Number"
Me.TextBox_JobNumber = ""
Exit Sub
End If

'Lookup based on first control
With Me
.TextBox_LotNumber = Application.WorksheetFunction.VLookup((Me.TextBox_JobNumber), Sheet1.Range("Lookup"), 4, 0)
.TextBox_ProductNumber = Application.WorksheetFunction.VLookup((Me.TextBox_JobNumber), Sheet1.Range("Lookup"), 5, 0)
.TextBox_PartName = Application.WorksheetFunction.VLookup((Me.TextBox_JobNumber), Sheet1.Range("Lookup"), 6, 0)
.TextBox_DrawingNumber = Application.WorksheetFunction.VLookup((Me.TextBox_JobNumber), Sheet1.Range("Lookup"), 7, 0)
.Textbox_Customer = Application.WorksheetFunction.VLookup((Me.TextBox_JobNumber), Sheet1.Range("Lookup"), 8, 0)
.TextBox_Order = Application.WorksheetFunction.VLookup((Me.TextBox_JobNumber), Sheet1.Range("Lookup"), 9, 0)
End With
With Me
TextBox_End.Text = Now()
End With
End Sub