Updating a combobox selection

[fbiasi]

Hi there,

I have a userform used to input data. I contains textboxes and comboboxes
and a command button that is clicked on to record the data onto a worksheet.

What I am looking for is a combobox that is linked to a list of clients (column A of worksheet "Clients").

I would like the user to click on the combobox and select a client name. If the
client is new then I would like the user to be able to type in the new name and,
then have that name available in the "Clients" names that appear the next time
the user clients on the combobox.

I have it almost working, but not quite.

I have a defined name on the "Clients" worksheet as follows:

Please Login or Register  to view this content.

I have the same code as the RowSource for the combobox

What happens now is that the first time the combobox is selected, all the client names
appear in the combobox, but if a new name is added, the combobox doesn't grow in size.
Instead the last entry is removed.

The command button code is below.

Please Login or Register  to view this content.

[p24leclerc]

It would help us a lot if you attach your workbook with sample data and macros.

[fbiasi]

Thanks for responding Pierre,

I have created a simplified workbook with some generic data. A little info on what I am trying to so:

We have workbook clients with 9 columns of information. I am creating a userform for us to use to fill in the information. The form I created has text and combo boxes.
I have simplified the workbook attached, but the idea is the same. The workbook has 3 sheets "Log", "Enter", and "Clients". The data is filled in with the text or combo box.
When the "Enter Client" command button is clicked, the data is copied from the "Enter" sheet to the next available row on the "Log" sheet. The "Log" sheet is used as a log of
all the entries made (some fields may repeat, but not all of them).

Then the client name is copied from the "Enter" sheet to the next available cell of column "A" on the "Clients" sheet. The names are then sorted alphabetically and duplicate
entries are deleted. I have a defined name range called "ListClients" that is used as a source for the combo box. This is where the error happens.

At this point, everything works. The 'Enter Client' does all it's supposed to. The problem is the named range "ListClients" doesn't update properly after the first client
'new' client name is added to the list. If the Client name that is added already exists (is already part of the "ListClients" nothing needs to happen, but if the name is a new one
(for this example, Frank is not a client name and would be 'new'. When that is entered, it gets added to the "Log", added to the "Client Names" and the list is sorted, but then next
time the form is used, the last name in the "ListClients" named range does not appear (in this case, "William').

That's the problem I am having. Sorry for being so long winded. I hope this makes sense. If there is an easier way of doing what I am attempting, I would appreciate hearing about it.

Thanks in advance.

Frank

[fbiasi]

My apologies.

Please ignore the previous attachment.

USE THIS INSTEAD.

[p24leclerc]

Here is what I found. It makes the combobox working now.
First, your second sort lines of code had Header = xlYes but it should be xlNo because you are not selectingthe titles' row.
Second, I added a line defining again the row source of the combobox so it is updated to its new dimension. I remarked that if you enter 3 new names, you were loosing the last 3 clients name in the combobox. Probably because it was not updated unless you close the form and reload it again.

Please Login or Register  to view this content.

[fbiasi]

Thanks...just tried it out and yes it works perfectly...thanks for your help...