Hi,

I have a UserForm which Adds records to a database.

I want to stop the user from entering in the 'Unique Reference Number' text box any number/text that has been used previously as an identifier. The previous identifiers are stored in Column A of my spreadsheet. The Unique Reference Number box is called 'NameTextBox' in my code.

I also want to stop the user from being able to leave this textbox blank.

If either of these things happen I want it to return a specific error message and then make the user go back and change their answer until it is either complete and unique.

Any help is greatly appreciated, my code is outlined below.

Many Thanks

David


Private Sub CommandButton1_Click()

UserForm.Show

End Sub

Private Sub CancelButton_Click()

Unload Me

End Sub

Private Sub ClearButton_Click()

Call UserForm_Initialize

End Sub



Private Sub OKButton_Click()

Dim emptyRow As Long

If Not IsDate(TextBox12.Value) Then
               MsgBox "The initiation date you have specified is not valid", vbCritical, "Error message"
               Exit Sub
          End If


'Make Sheet1 Active
Sheets(1).Activate

'Determine EmptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Export Data to worksheet
Cells(emptyRow, 1).Value = NameTextBox.Value
Cells(emptyRow, 2).Value = TextBox25.Value
Cells(emptyRow, 3).Value = PhoneTextBox.Value
Cells(emptyRow, 4).Value = ComboBox3.Value
Cells(emptyRow, 5).Value = ComboBox4.Value
Cells(emptyRow, 6).Value = TextBox1.Value
Cells(emptyRow, 7).Value = TextBox26.Value
Cells(emptyRow, 8).Value = TextBox2.Value
Cells(emptyRow, 9).Value = TextBox3.Value
Cells(emptyRow, 10).Value = TextBox4.Value
Cells(emptyRow, 11).Value = TextBox5.Value
Cells(emptyRow, 13).Value = ComboBox6.Value
Cells(emptyRow, 15).Value = TextBox8.Value
Cells(emptyRow, 16).Value = TextBox9.Value
Cells(emptyRow, 17).Value = ComboBox5.Value
Cells(emptyRow, 18).Value = TextBox11.Value
Cells(emptyRow, 20).Value = TextBox13.Value
Cells(emptyRow, 21).Value = TextBox14.Value
Cells(emptyRow, 22).Value = ComboBox2.Value
Cells(emptyRow, 23).Value = TextBox16.Value
Cells(emptyRow, 31).Value = TextBox24.Value

If IsDate(TextBox12.Value) Then
     Cells(emptyRow, 19).Value = DateValue(TextBox12.Value)
Else
     Cells(emptyRow, 19).Value = "invalid"
End If


End Sub




Private Sub UserForm_Initialize()

'Empty NameTextBox
NameTextBox.Value = ""

'Empty TextBox25
TextBox25.Value = ""

'Empty PhoneTextBox
PhoneTextBox.Value = ""

'Empty TextBox1
TextBox1.Value = ""

'Empty TextBox26
TextBox26.Value = ""

'Empty TextBox2
TextBox2.Value = ""

'Empty TextBox3
TextBox3.Value = ""

'Empty TextBox5
TextBox5.Value = ""

'Empty TextBox8
TextBox8.Value = ""

'Empty TextBox9
TextBox9.Value = ""

'Empty TextBox11
TextBox11.Value = ""

'Empty TextBox14
TextBox14.Value = ""

'Empty TextBox16
TextBox16.Value = ""


'Empty TextBox24
TextBox24.Value = ""

'Empty TextBox4
TextBox4.Value = ""

'Empty TextBox13
TextBox13.Value = ""

 
   
'Empty ComboBox2
ComboBox2.Clear
     
'Fill ComboBox2

With ComboBox2
     .AddItem "3"
     .AddItem "4"
     .AddItem "6"
     
End With
    
'Empty ComboBox3
ComboBox3.Clear

'Fill ComboBox3
With ComboBox3
    .AddItem "3"
    .AddItem "4"
    .AddItem "6"
End With

'Empty Combobox4
ComboBox4.Clear

'Fill ComboBox4
With ComboBox4
    .AddItem "Left"
    .AddItem "Right"
End With
     
     
'Fill ComboBox5
With ComboBox5
    .AddItem "Yes"
    .AddItem "No"
    
       
End With


'Empty Combobox6
ComboBox6.Clear

'Fill ComboBox6
With ComboBox6
    
.AddItem "Spain"
.AddItem "France"
.AddItem "Germany"
   
End With


    With TextBox12
        .Text = "dd/mm/yyyy"
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.Text)
 End With
 

     
'Set Focus on NameTextBox
NameTextBox.SetFocus

End Sub