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

Quote from royUK

One major issue that I have found is not VBA related. Your Table(Listobject) extends way beyond the recorded data, so lr is returning 499 in the frmAdd code. Your columns are the same.

I defined the table to range A1:E500 for the sample file I attached here.

But the final table will have even more columns and rows...

If this is a problem then I should tell the code to only look in the occupied cells, but how do I do that?

Thank you again

Quote from royUK

You don't need empty rows and columns in a Table. As you add them then the Table resizes.

Good reading is

Overview of Excel tables

Ok, noted, read the link. BUT, the frmAdd works perfectly without my TB_Change_Event, even with the table sized that way, and with the emtpy rows.

SO it must be my change event code in the TB Client that is incorrect, I missed something or gave the wrong ranges to look into...

Quote from royUK

This is how I would write the code to add a line

Code

Option Explicit ' if you don't know what this is read http://excel-it.com/option_explicit.htm
Private Sub Butt_Add_Click()
    Dim oTbl As ListObject
    Dim TblNewRow As ListRow
    
    Set oTbl = ThisWorkbook.Sheets("Clients").ListObjects("tblClients")
    
    'CHECK THAT A CLIENT NAME HAS BEEN RECORDED
    If Not TB_Cliente = "" Then
        With ThisWorkbook.Sheets("Clients").ListObjects("tblClients")
            
            Set TblNewRow = oTbl.ListRows.Add
            
            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

Display More

Quote from royUK

Which form is your last posted code in?

In frmAdd.

I tried to put your code into the frm Add but it highlights ctrl and Private sub line...

Quote from royUK

Your code does not appear to be using a ListObjects properties correctly. Have a close look at how I added a new entry in the code above.

I did, thank you very much. You defined the Listobject as oTbl.

But your code does not add the new row under the last row occuied by that specific client, and that would be a major issue for me.

I know I somehow have to add a AND (do add the row under the last one occupied by than client's name AND also fill the QUality and Count textboxes with the values of lr, 4 and lr, 5.

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)
'Add line to tell it that if he found the client name in the listobject, than besides adding the row below the last one of that client, also fill the TB-Quality and TB-COunt with the values of (lr,4) and (lr,5)
            Else
                Set TblNewRow = .ListRows.Add
            End If

Quote from royUK

Probably because you haven't used Option Explicit. You also need to resize the Table

I did use Option explicit.

Also, I downloaded the file you attached, still the debug highlights the ctrl and does not let me enter the new row from the userform, not does it autofill textboxes.

CAn you please help with the change event, some suggetions if possible?

Thank you

Quote from royUK

Where is this change event?

I can see what is happening now. I added Option Explicit just before uploading. You haven't declared ctrl as a variable. You need to add

Code

Dim ctrl As MSForms.Control

Ok, with the crt variable added it works, but it has eliminated the most important feature of the code : it add the new client row to the next available row in the table, whereas my original code added an already exisiting client to the ow below the last one of that client.

I realize I posted a lot and maybe my request got lost.

I need the code IF the client name inserted in TB-CLient has already been recorded in the sheet, to also autofill the textboxes QUality and Count with the values already registerd in the last row with that client name (client name is always in column A)

Hi RoyUk hi everyone, have a nice Monday.

I just wanted to let you know I created myself a Change_event n my userform, as your solution below, when inserted in the code, below the For Loop, it worked but of course it filled the txboxes only when I clicked the butt. Add (logically, as the lines were in the code of the Sub Butt_Add_Click.

me.textbox.name=ThisWorkbook.Sheets("Sheetname")ListObjects("tblClients").DataBodyRange(RowCount, 1).Value

What I did I created and extra Combobox on the useform frmAdd, and I gave this CMBX a CB_CHange_Event

I post the code might help somebody else too.

Private Sub ComboBox1_Change()
'Define the range variable
    Dim xRg As Range
'Define the range where the code should look for the list to assign to the combobox
    Set xRg = Worksheets("Sheetname").Range("A:A")
    TB_Name.Text = xRg.Cells(Me.ComboBox1.ListIndex + 1, 1).Value 'Assign whatever column the code needs to get the value from
    
    TB_Name.Text = xRg.Cells(Me.ComboBox1.ListIndex + 1, 4).Value
    TB_NAme.Text = xRg.Cells(Me.ComboBox1.ListIndex + 1, 5).Value
    
End Sub

I forgot to add the Sub Initialize I had to use for the CBX, befor the change_event code. Sorry, here it is

Private Sub UserForm_Initialize()
'Define the range variable
Dim xRg As Range
'Define the target column/range where the CB has to look for values to assign to its list    Set xRg = Worksheets("Sheetname").Range("A:A")    Me.ComboBox1.List = xRg.Columns(1).Value
End Sub 

Thank you for the correction RoyUk, yes, the set line was on the same line with the comment, error from copy/paste code.

As for the whole column thing, that is correct, but it is the only solution I could think of and put in practice. As per my very frist posts in this thread I had been asking for a change event code, that is what I needed.

Of course the solution I found is "rough", but i am a beginner; and it works.

Should I find another solutin or understand how to tweak the present Initialize Sub in order for it to load only the names I type in the CMBX, I will post. it.

If you have any suggestions, or anyone else, of course it would be highly appreciated.

Thanks anyway