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.

'--------------------------------
'DECLARE AND SET VARIABLES
Dim dnary As Object, col As Integer, I As Long
col = 4 'CHANGE TO THE COLUMN OF THE DELIVERY ADDRESS ON SHEET1
Set dnary = CreateObject("Scripting.Dictionary")
LastRow = ActiveSheet.Cells(Rows.Count, col).End(xlUp).Row
'--------------------------------
'LOAD COMBOBOX
    With ComboBox1 'CHANGE TO NAME OF YOUR COMBOBOX
        .Clear
        For I = 2 To LastRow
            If Not dnary.Exists(Cells(I, col).Value) Then
                dnary.Add Cells(I, col).Value, 1
                .AddItem Cells(I, col)
            End If
        Next I
    End With
'--------------------------------
'CLEANUP
dnary = Nothing

HTH,
Maud

[gmr4evr1]

Maudibe,
I think you might need to add

Dim LastRow as Long

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?