Copy ComboBox entry if typed in & if it doesnt aready exist in column

[chiggerz]

Hi all, hope you can help me. I have created a transport request form for work, but trying to tidy it up a bit and make things better for any future requests.

So basically, I have a userform which is now fully working and copies all the info into the relevant cells in "Sheet1".

I have another sheet "Data" which contains all the data for the combo boxes on the userform. What I need is when someone types in the combo box and the info (in this case, delivery address) is not in the list already, then when clicking submit, as well as copying that info into the relevant field (which currently works), I also need it to copy it into the next blank row in column B in sheet "Data", so next time someone uses the form, that is now an option in the combo box.

It doesn't necessarily need to do this when submitting the userform as I have another button on the sheet that saves the form as a PDF, so I could add it to this code if it makes any difference?

Hope this makes sense.

Thanks in advance.

[Maudibe]

Chiggerz,

There is no need to maintain your data sheet to populate the combobox. Unique values for the delivery address combobox can be loaded right from sheet 1. Add the following code to your userform's Activate event routine while removing any other code you already have in place to load the combobox. Change the col=4 to the column that holds the delivery address on sheet 1. Change the name of the combobox to your combobox name on the line with the "With" statement.

When the code runs, it will cycle down the column of the delivery address and additem only the unique values.

Please Login or Register  to view this content.

HTH,
Maud

[gmr4evr1]

Maudibe,
I think you might need to add

Please Login or Register  to view this content.

to your code? I had to when I tried to use it.

[Maudibe]

If you are using Option Explicit in your code then yes you will have to declare it.

[gmr4evr1]

I kind of thought that but wasn't sure. I haven't quite figured out everything that must be declared when using Option Explicit......other than everything?