messagebox based on value userform

[ryan_law2000]

In a userform I have textbox1
If this textbox = any number in column B Sheet1
then have a message box appear saying " repeat number"
then after pressing ok
delete the number from textbox1

 

[Tom Urtis]

You did not say what you want to have happen if the number is not in column B of Sheet1 so taking your question literally, and assuming what you are "pressing ok" to is CommandButton1, this will do what you ask:

Private Sub CommandButton1_Click()
Dim strText&, dblVal#, varFind As Variant
strText = TextBox1.Text
dblVal = Val(strText)
Set varFind = _
Sheets("Sheet1").Columns(2).Find(What:=dblVal, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not varFind Is Nothing Then
MsgBox strText & " is a repeat number.", 64, "Duplicate"
TextBox1.Text = ""
End If
Set varFind = Nothing
End Sub