I am using the following within my spreadsheet (Excel Version 14.0.7015.1000) 32 Bit. It adds a value to the next empty cell within column C. I would appreciate if someone could show me how to modify this such that it throws a text error if the value is not unique to column C and then the offending cell is selected. I tried using the built-in data validation but it seems to be bypassed when using the code below.

My excel vba level is currently "Dangerously Ignorant" so I'm happy to clarify anything you need to help.

Private Sub TextBox1_Change()

'Debug.Print "Outside " & IsActive

'To prevent recursion
If Not IsActive And TextBox1.Text <> "" Then
IsActive = True
Application.OnTime Now + TimeValue("00:00:03"), "Module2.CopyToCell"
End If

End Sub

_______

Private Sub UserForm_Initialize()
IsActive = False
TextBox1.SetFocus
End Sub


_________

Dim IsActive As Boolean

Sub CopyToCell()

Dim Ws1 As Worksheet
Dim LastCellInRng As Range

Set Ws1 = Worksheets("Main")
Set LastCellInRng = Ws1.Range("C" & Ws1.Cells.Rows.Count).End(xlUp)
LastCellInRng.Offset(1, 0).Value = UserForm2.TextBox1.Text

UserForm2.TextBox1.Text = ""
UserForm2.TextBox1.SetFocus
IsActive = False

End Sub