VBA Userform- autofill textboxes if reference already exists in the sheet

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

Quote from Carim

Hello,

In order to make things a lot easier for anybody who might help you out ... you should attach a sample file ...

Carim you're right. I hereby attach the sample file.

Now Client name is in column A. The code of the Add Client userfrom already checks if a Client name ahs already been recorded and if so, it adds the new row with the same client name right below the last row with the same client name.

I need it to also autofill the textboxes Product, Quality and Count (in the sheet it will automatically fill C to E columns) with the information from the last row with tha client's name, IF, of course, that client and row already exist.

While column B must remain clear as Cases will change for every row relative to that client.

If the client name has not been yet recorded than the userform should proceed (as it already does) to let me fill manually all the textboxes/cells from B to E and add the row in the first empty row on the sheet (it already does this so it is ok).

Quote from Carim

Thanks a lot for your sample file

There are Six userforms in your workbook ...

So, which one are you referring to ...???

Carim I am reffering to frmAdd , this is how it is named in the code (the command button that calls this userform is in the CONTROL sheet and is named Add New Clients.

This is the only one I want to modify as per my thread.

royUK, thank you very much for your reply and file. I already found your Combobox Lookup file, yesterday. I am doing my homework as I want to become an intermediate VBA programmer sometimes in the future so before posting a thread I go searching the forums for similar codes and I tweak modify and try to tailor codes I find on my needs. Sometimes though I still need help.

Your Combobox Lookup is great and it helped me tremendoulsy as it allowed me to understand how the code works and I also integrated it to one of my other projects. But unfortunately, my Combobox in this particular project would become unmanageable as clients record will increase up to 1500 names...

However, if you think I might maybe tweak it somehow, or make it function with a Yes/no list...please, any suggestion is really precious to me.

Quote from Carim

If you want to modify the UserForm frmAdd

Do we agree that you need a Sub TB_Cliente_Change() ...?

Carim yes absolutely, I need a SUb Tb_Cliente_Change, I confirm, thank you so much in advance!

Quote from royUK

I would strongly recommend assigning a unique ID number to each name.

Take a look at my DataBaseForm, it's free and there's a link at the bottom of my post. This finds all matching entries and loads them to a ListBox, from which you can click on a name to add to the individual TextBoxes.

royUK, I have already downloaded that too and played with it.

Now assigning a unique ID to each client, would mean changing almost all the table format and subsequently the code again, and my previous work on the userform I already have would be gone.

besides I have to think the projects for the users (which are often basic excel users), so that they have it simple. Adding ID's for clients name would also mean that I have to tell the userform to add these for me in yet another sheet...

It would become much more complex than it already is, as I see it.

I would rather work and understand completely my existing userform, as I have worked days to it to understand it and tweak it ( it is a big sattisfaction form me to have been able to do so). Not only, this specific userform, already does the things I need it to perform for the existing file, I only need to see how can I add the change event, so that it autofills the specified textboxes if record already exists.

I am anyhow studying and playing arround with your Database file, Combobox file, and one more file with a marvelous userform you have made for another user (Plant file). Those are files and codes I am studying and plan to understand and integrate in new projects so that I can improve my VBA and grow.

Thank you very much to you too

Quote from royUK

I'm not sure what the problem is with IDs. They can easily be added into the database with code.

The problem that I can see is that you have multiple identical names, so any search will produce several results, therefore the results needs to be shown in some way, i.e. a ListBox

That is the point. This workbook will contain many rows (up to 10-15 rows) with the same client name in the first colum, as a client may have many stages until client project is completed. So that is why this userform I already have does the dirty work for me, and the ammed userform does the other work, meaning the user can ammend some fields later, when the data has been provided from the other department, and it also does the Listobjects display, so it really suits my needs.

regarding the ID's, I know they can be added to the code, but how it is still very hazy for me. After I will understand how change event in a ctrl box works if Carim will be so kind as to help with this, I can then concentrate on how to code ID's (consequantial ID) .

But for me is all searching , studying, understanding and then trial and error 8many) of course.

Quote from royUK

I can see you have frmAmendList which allows you to display all Brad's records and that can populate the frmAmend. In which case I'm not sure what you need.

I would suggest that you don't loop through cells to populate the Listbox. Look at some of my recent UserForms posted here which load the ListBox by using AutoFilter which is potentially much faster.

Thanks RoyUk. I took a look at your most recent userforms, and they are indeed very useful for one to learn or modify for different kind of projects, But in this case I already have my userform, which I already tailored to my needs.

As I wrote in previous posts in this thread what I need is few lines of code that will add an change event in my frmAdd , specificaly in my Tb_Cliente ctrlbox. All the rest of the userform and the other two userforms (frmAmmend and Ammedlist) work perfectly, as I intentionally modified the code to serve my project.

I can see you would rethink the whole code and project , but that is not my intention.

Quote from royUK

If you want check if a name exists in the list use Application.WorkSheetFunction.CountIF to check if the count is > 0.

If you need help using COUNTIF in VBA post back, but I'm sure that you would rather learn for yourself.

royUK, that s correct. I mean I often have to ask for help, but first I like to stay hours and see if I can do it myself, there is in fact great satisfaction. As for all your other files quoted and suggestions, I totally confirm they are of great value and I am already palying with them so that I can see how they work, tweak them and learn even more VBA

As far as the countif function, the userform already checks is the client exists with these lines (code below) so in this case I really only edd the Tb_Cliente_change event to copy/autofill if client exists. However, I will start a mini sample project and try to write myself a counif function just to see if I am able to do it myself. If by tomorrow I see I cannot figure it out, I will ask for your kind help.

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

Quote from royUK

That's no problem.

You will find coding easier if you use one userform, maybe with a MultiPage.

You seem to be doing great so don't hesitate to ask for help.

Hi royUK, as promissed I tried to work on my own code, learning from all the example userforms you have posted. Unfortunately, those are based on combobox selection (very neat code btw, clean welle comented, so great work and very precious)

Now, I tried to look in my 3 userforms how they work, how are the ranges defined and how the countif works for them and I tried to put toghether my TB_Cliente_Change event. Of course it doesn't work , because the "range" or wht shoud have been the offset is a mess (I suppose).

Could you please help me? I tried to tell the code: if the value I input in textbox TB-Cliente matches a value already existant in the Datalistobjects in the Clients sheet/table in column A, THEN fill the textboxes Quality and Count with the values you find in the last row you found with the Client name, specificaly column 4 and 5.

I also thought maybe a change event for the TB-Client is not even necessary. It could be ust a line addedto the form frmADD, under the countif where the code already checks IF the client name already exists ...

Please, can you help me to sort this code out, or write a new one? Any suggestion or help would be very precious to me. It is by now driving me insane

Private Sub TB_Cliente_Change()
If Not TB_Cliente = "" Then
        With ThisWorkbook.Sheets("Clients").ListObjects("tblClients")
            lr = .DataBodyRange.Rows.Count
Set rng = .Range(.Cells(2, 1), .Cells(lr, 1))

            For rowCount = lr To 1 Step -1
                If .Range(rowCount, 1) = TB_Cliente.Value  Then 
        .TB_Quality = .Range(rowCount, 4)
        .TB_Count = .Range(rowCount, 4)
            End If
End Sub