Hello everyone and good day to all.
I manged to put togheter a code for userform instead of letting users fill the tables from the sheet.
Now the userform works fine, as I tweaked and modified the code untill it fit my needs.
1. When one inputs a client's name in the Txtxbox Cliente, the code checks IF that client's name has already been recorded in the "Clients" sheet, and if so, it adds the new row in sheet Clients but exactly under the last row with that specific Clients's name, This was one of the most important things I needed for this userform to do.
2. I need however, to also tell the Userform: if the client's name I input in textbox Cliente , already exists in the sheet "CLients", then also automatically fill the textboxes Product and QUality with the information that already exists in the last row with that client's name.
Basically I need the userform to copy the information from cells C to E from the last row that has that specific Client's name in Column A.
Otherwise if the client's name doesn't exixst in the sheet yet, the userform should let me fill all the textboxes (it already does this).
I think I need a change event but I have no idea how to implement a change event in a userform nor how to write this kind of event.
Any help will be highly apreciated, thank you in advance.
Here is the code I have:
Private Sub Butt_Add_Click()
newrow = 0
'CHECK THAT A CLIENT NAME HAS BEEN RECORDED
If Not TB_Cliente = "" Then
With ThisWorkbook.Sheets("Clients").ListObjects("tblClients")
lr = .DataBodyRange.Rows.Count
For rowCount = lr To 1 Step -1
If .Range(rowCount, 1) = TB_Cliente.Value And .Range(rowCount, 3) = TB_Product Then
newrow = rowCount
Exit For
End If
Next rowCount
'ADD A NEW ROW TO THE CLIENTS TABLE AND ASSIGN THAT AS THE ROW YOU'RE ABOUT TO RECORD DATA IN
If rowCount <> 0 Then
Set TblNewRow = .ListRows.Add(rowCount)
Else
Set TblNewRow = .ListRows.Add
End If
With TblNewRow
'STAMP THE DATA TO THE NEW ROW ADDED TO THE END OF THE CLIENTS TABLE
.Range(1, 1) = TB_Cliente
.Range(1, 2) = TB_Cases.Value
.Range(1, 3) = TB_Product
.Range(1, 4) = TB_Quality
.Range(1, 5) = TB_Count.Value
End With
'CLEAR THE FORM EXCEPT FOR THE CLIENT FIELD
For Each ctrl In Me.Controls
If TypeName(ctrl) = "TextBox" And ctrl.Name <> "TB_Cliente" Then ctrl = ""
Next ctrl
'PUT THE CURSOR IN THE CASES FIELD READY FOR ADDING A NEW ROW USING THE SAME MANIFEST NUMBER
TB_Cases.SetFocus
End With
Else
'MESSAGE BOX TO ALERT THE USER THEY HAVEN'T FILLED OUT THE CLIENT NAME
MsgBox "You must enter a Reference ID.", , ""
End If
End Sub
Private Sub Butt_Finish_Click()
'SHOW A MESSAGE BOX ASKING IF THE USER IS DEFINITELY FINISHED
response = MsgBox("Are you sure you've finished?", vbYesNo, "All done?")
'IF THEY SAY YES, CLOSE THE FORM
If response = vbYes Then Unload Me
'IF THEY SAY NO, THEY GO BACK TO WHERE THEY WERE BEFORE THEY HIT THE FINISH BUTTON
End Sub
Private Sub TB_Product_Change()
End Sub
Private Sub UserForm_Click()
End Sub
Display More