OssieMac...This worked with just one small change. Kept getting a "NULL"
error when I got to CBName.Value. Changed it to CBName.Text and it worked
great including the inserting of new names.

Thanks

"OssieMac" wrote:

Hi John,

With the following code you need to initially name one cell only as
Cardholder. The code assumes this cell to be Cell A1 on worksheet Carddata.

As the names are added in the combo box, if not already existing, they are
added to the top of the list and the named range for the combo is redefined
with the expanded range.

The RowSource property for the combo needs to be set to Cardholder. (Can't
do this until after cell is named Cardholder.)

The MatchRequired property for the combo needs to be false.

Do a Find and Replace on the following code to change the sub name from
ComboBox1 to match the name of your combo box.

Private Sub ComboBox1_AfterUpdate()
Dim comboEntry As Variant
Dim rngTofind As Range
Dim lngRows As Long

'Save the Combo box entry to a variable
comboEntry = ComboBox1.Value

'Test if combo box entry exists in Rowsource
With Sheets("Carddata")
Set rngTofind = .Range("Cardholder") _
.Find(What:=comboEntry, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
End With

'If entry not found then add it to named range
If rngTofind Is Nothing Then
lngRows = ComboBox1.ListCount + 1

With Sheets("Carddata")
'Move existing Cardholder range down 1 cell
'to allow new entry to be at top of range.
.Range("Cardholder").Cut _
Destination:=.Range("A2")

'Redefine Cardholder range to include
'additional cell for new combo box entry.
.Range(.Cells(1, 1), _
.Cells(lngRows, 1)) _
.Name = "Cardholder"

'Populate the first cell in the named range
'with the new combo box entry.
.Cells(1, 1) = comboEntry
End With

'Update the Rowsource for the combo box
ComboBox1.RowSource = ("Carddata!Cardholder")
End If

End Sub


--
Regards,

OssieMac