Update Sheet Based On Entry In Different Sheet

[BeanCounters]

Hi All,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Need a little help re-working a code. I need to have data entered in my data entry worksheet update my client list worksheet if the client name does not already exist in the client list worksheet.


The code I posted below worked great when I was using a combobox with Data Validation. I have since changed to using a combobox with ActiveX so I could use the autocomplete feature. Can anyone advise on how to get this code to work with cbo ActiveX?


Am I correct in thinking it has something to do with the “Set rngDV=Cells.SpecialCells(xlCellTypeAllValidation)? I have the code inside of my data entry worksheet if that means anything.
<o></o>
<o></o>
<o>

Option Explicit
' Developed by Contextures Inc.
' [URL="http://www.contextures.com"]www.contextures.com[/URL]</o:p>
<o:p>Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim str As String
Dim i As Integer
Dim rngDV As Range
Dim rng As Range</o:p>
<o:p>If Target.Count > 1 Then Exit Sub
Set ws = Worksheets("Client List")
 
If Target.Row > 1 Then
  On Error Resume Next
  Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
  On Error GoTo 0
  If rngDV Is Nothing Then Exit Sub
 
  If Intersect(Target, rngDV) Is Nothing Then Exit Sub
 
  str = Target.Validation.Formula1
  str = Right(str, Len(str) - 1)
  On Error Resume Next
  Set rng = ws.Range(str)
  On Error GoTo 0
  If rng Is Nothing Then Exit Sub
 
  If Application.WorksheetFunction _
    .CountIf(rng, Target.Value) Then
    Exit Sub
  Else
    i = ws.Cells(Rows.Count, rng.Column).End(xlUp).Row + 1
    ws.Cells(i, rng.Column).Value = Target.Value
    rng.Sort Key1:=ws.Cells(1, rng.Column), _
      Order1:=xlAscending, Header:=xlNo, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
  End If</o:p>
<o:p>End If</o:p>
<o:p>End Sub

</o>
<o></o>
<o></o>

To further complicate my life, boss also wants this to verify if we really want to add new client to the list. Perhaps a msgbox that asks “ ‘new client name’ is not in the list. Do you want to add?” ('new client name' would show as the name you just entered). Just need that extra step to prevent misspellings and tabbing out of the field without checking their entry.
<o></o>

Thank you for any help you can provide.
<o></o>

Sincerely,<o></o>
Julie<o></o>