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

**royUK** · Apr 26th 2021

I don't know what change event you want. Loading a whole column into a combox is not a good idea.

clairexcel · Apr 26th 2021

Quote from royUK

I don't know what change event you want.

This is strange, because I repeatedly said it. Anyways, I wanted a TXtbox or CMBx change event, so that whn one types in an exixting client (existing in column A in the Sheet Clients as per my fsample file) the etxboxes Quality and Count would autofill with the information existing in columns D and E as those info emai the same for the Client.

Quote

Loading a whole column into a combox is not a good idea.

Yes, that may be the case, so if you have any suggestions on how to improve the code, as I already asked, that would be of great help.

Thank you.

**royUK** · Apr 26th 2021

And I have asked which combobox and/or TextBox. Also, I don't think the Change event is a good idea, but good luck with it.

clairexcel · Apr 26th 2021

Quote from royUK

Also, I don't think the Change event is a good idea, but good luck with it.

Noted, Then again, what would you have done to autofill the texboxes that remain the same for the existing Clint, but then also, add a new row?

The purpose of my thread was finding a solution to this conundrum, however, besides the one I found and which, I agree with you may not be perfect or a great idea, but I did not had any other suggestions for this purpose.

so again, if a change event is not a good idea, what would be?

**royUK** · Apr 26th 2021

Attach your latest version.

clairexcel · Apr 26th 2021

I attach my latest version of my file, with the combobox added. to the frmAdd.

I emphasize that the purpose of adding the combobox is: if the user wants to Add a row for an already existing client, then by typing or choosing the Client's name (typng it into the TB_Cliente or selecting it from the Combobox whatever you suggest) , the TB-QUality and Tb_Count should autofill with already existing values, because those info never change for existing clients, only Cases change.

Thank you

**royUK** · Apr 26th 2021

Which version of Excel are using. Do your users have Office 365?.

clairexcel · Apr 26th 2021

Quote from royUK

Which version of Excel are using. Do your users have Office 365?.

I am currently working with Excel 2007, although my final users do have Office 365, yes

**royUK** · Apr 26th 2021

This loads the clients into the combobox

Code

Dim xRg As Range
    Set xRg = Worksheets("Clients").Range("A:A")
    Me.ComboBox1.List = xRg.Columns(1).Value

There's no need to repeat it in the Change event.

clairexcel · Apr 26th 2021

Noted, but I still need the change event (the only method I could think of) to autofill the etxboxes indicated, IF the client already exists.

Now, you said the change event is not the thing I need. Please advice what could be a better solution to autofill textboxes when inserting row for already existing client.

**royUK** · Apr 26th 2021

Your combobox is loading all transactions . For your change event to work it relies on the user selecting the correct entry. I think you want the last entry by Brad.

clairexcel · Apr 26th 2021

Quote from royUK

For your change event to work it relies on the user selecting the correct entry. I think you want the last entry by Brad.

Nailed it Yes that it's correct observation, but again, that exceeds my vba programming abilities.

**royUK** · Apr 26th 2021

You can't use the ListIndex of the ComboBox unless you stick with having multiple entries for each client. This is why the ID system would help.

Also, you don't need the TextBox for the client name.

**royUK** · Apr 26th 2021

I think this does what you want. You can remove TB_Client

clairexcel · Apr 26th 2021

Whoa, basically you have rewritten the code for the userform. This code is simply awsome: shorter, and most of all it only shows the last entry of the clients names... I never would have been able to do it by myself.

I now have to study the code, the dict., key and trim functions ( I ignored their existance) . Because I can see that the it is the TRIM function that does the trick of only showing the wanted entry in the cmbx list.

My solution code I posted today, is in comparison to yours like sloppy patches to fine stiches,...

Thank you so very much. for your inputs, links and code! I will most definitely bother you if I can not understand the trim function well.

Very apreciated royUK, hope to be able to pay it further!

**royUK** · Apr 26th 2021

The load ComboBox solution is from my website - Add A List of Unique Values to a Combo Box

I used Trim because your cells have the data centralised so it has empty spaces before it.

clairexcel · Apr 26th 2021

Quote from royUK

The load ComboBox solution is from my website - Add A List of Unique Values to a Combo Box

I used Trim because your cells have the data centralised so it has empty spaces before it.

These days I have to do an extensive study on your website.

A hundred likes and thanks! royUK

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

Participate now!

Share