ComboBox and .Find

[dylcon]

Warning: code will be infinite loop!

Hello, I have multiple comboboxes in a userform and I want the second combobox list to be dependent upon the first combobox. The first combobox simply goes through a spreadsheet and adds the cell values from the first column. I want the second combobox to add items that are next to the cell that matches the value of the selected item from the first combobox. I currently have

Please Login or Register  to view this content.

but this is not working. It is difficut to explain what I would like exactly, so I will upload the workbook as well, if needed.
Thanks in advance!

[Norie]

Why not just loop through the range that has the values you want in Combobox2?

If a value has the selected value from Combobox1 adjacent to it then add it to Combobox2.

[dylcon]

Good Thinking. I also realized a simple, silly error in my code that fixes it anyways. Your idea is much simpler though.

[dylcon]

Wait, I am not sure if that would work... Could you elaborate?

[Norie]

I'd need to know the columns/sheet(s) involved to post something specific.

Can you attach a sample workbook?

[dylcon]

Sure thing. It is on the OutgoingUF. And it uses the 'Schedule' WS.

Correct Document.xlsm

[dylcon]

The way I have it now works correctly, but there are duplicate 'Customers' showing up in ComboBox1, which is rather annoying. Although I understand that this problem would have to be addressed in a new thread.

[Norie]

Which form should I be looking at?

I think it's IncomeUF but I'm not sure.

[dylcon]

It is on the OutgoingUF. And it uses the 'Schedule' WS.

[Norie]

Try this.

Please Login or Register  to view this content.

[dylcon]

What is the advantage of this code vs. what I have done? Is it more efficient?
Thank you very much for putting time into looking at it though, I greatly appreciate it.

[Norie]

It doesn't list the customers multiple times in the first combobox.

[dylcon]

It doesn't seem to be working when I run it...

[Norie]

It works when I run it.

How exactly did you use it? It should replace the existing code for UserForm Initialize and Combobox1_Change.

[dylcon]

Yes, that is what I did. That's odd. Is there any other way to remove duplicates from the combobox? Other than that, my code works fine.

[Norie]

The code I posted prevents duplicates in combobox1, see the attached file.

[dylcon]

I have just downloaded your version and the first and second comboboxes are not loading. This is very peculiar.

[Norie]

Which userform are you looking at?

The code was for OutgoingUF, as you indicated here that was the one of interest.

It is on the OutgoingUF. And it uses the 'Schedule' WS.

[dylcon]

Capture.jpg
Yes, as you can see here, when I click the combobox, nothing shows up

[Norie]

In the original code there's a missing dot before Cells in this line of code.

Please Login or Register  to view this content.

I should have picked it up but missed it.

So if you add that missing dot in the code I posted the code will work.

[dylcon]

That worked much better. Thank you. For some reason I thought I would simply be able to copy the subprocedure and change the names around, but that's not working. Any chance you know what I did incorrectly? This is to fill up the third ComboBox with the PO Number, by the way.

Please Login or Register  to view this content.

[Norie]

Which range should you be loopng through?

This is setting it as column A, the customer column, starting at row 4.

Please Login or Register  to view this content.

Shouldn't you be looping through the Part# column, starting at row 2?

[dylcon]

Yes you are correct. So now I have this, but it is still not working... it has been a long day

Please Login or Register  to view this content.

[dylcon]

So I changed it to this:

Please Login or Register  to view this content.

but it only works some of the time. For instance, it works for the ADMO selection in combobox1, but not Stanley II.
Is this something to do with the

Please Login or Register  to view this content.

statement?

[Norie]

Have a look at the address of rng.

You can do that by putting a breakpoint on the End With, running the form, selecting from Combobox1 and then Combobox2.

The code will then halt and you can find the address by going to the Immediate Window (CTRL+G) and entering this.

Please Login or Register  to view this content.

[dylcon]

It has given me this:
$A$2:$B$13

[Norie]

Which I don't think is quite right.

In this code,

Please Login or Register  to view this content.

try changing .Cells(Rows.Count,1) to .Cells(Rows.Count,2), or to make it clearer .Cells(.Rows.Count, "B").

By the way, there's another problem - some of the part numbers on the worksheet are numeric but all of the part numbers in the worksheet are text.

That won't cause an error but it will prevent the code finding matches.

To deal with that change this,

Please Login or Register  to view this content.

to this.

Please Login or Register  to view this content.

[dylcon]

YES! Thank you so much!