Add to Dynamic List if it doesn't exist

[imalc]

I’m a beginner to VBA programing and I’m trying to enter a supplier into a combo box (cmbsupplier) on a user form which is populated by a Dynamic List (List2) and if it’s not in the list then add it.
Can anyone help me?

[Logit]

.
.
Presuming your dynamic list is located on Sheet 1 Col A beginning with A1 :

Paste this into the UserForm Initialize event -



Option Explicit


Private Sub UserForm_Initialize()
ComboBox1.RowSource = "Sheet1!A1:A100" & Range("A" & Rows.Count).End(xlUp).Row


End Sub



Keep in mind this is just one way of accomplishing the goal. There are others.

[mdmackillop]

Give this a try

Private Sub UserForm_Initialize()
Me.cmbSupplier.List = Range("List2").Value
End Sub


Private Sub cmbSupplier_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim Test As Boolean
Dim c
If cmbSupplier = "" Then Exit Sub
Test = False
For Each c In Range("List2")
If c.Value = cmbSupplier.Text Then
Test = True
Exit For
End If
Next
If Not Test Then
If Not AddData(cmbSupplier, "List2") Then
Cancel = True
Me.cmbSupplier.Text = ""
End If
End If
End Sub


Function AddData(data, DataList) As Boolean
Dim r As Range
Dim Chk As Long
Set r = Range(DataList)
Chk = MsgBox("Add " & data & " to list", vbYesNo + vbQuestion, "Add Data?")
If Chk = vbYes Then
r(r.Count + 1) = data
Set r = r.Resize(r.Count + 1)
r.Sort Key1:=r(1), Order1:=xlAscending
AddData = True
Else
AddData = False
End If
End Function