Message Box after a condition is met

[Akw47]

Hello so this is the code I currently have
Private Sub TextBox1_Change()
With ThisWorkbook.ActiveSheet
With .Range("D1", .Cells(.rows.Count, "D").End(xlUp))
If InStr(UCase(Me.TextBox1.Value), "/" Or ",") > 0 Then
MsgBox("Please confirm", vbYesNo, "Double Check") = vbAbort
End If
End Sub


Could someone help me edit it such that, in column D, if the cell has a "/" or "," the message box will pop out. If not nothing will happen thanks.
I will be typing something in the textbox eg. 12345. If it match and there is a "/" or "," with that number in the cell, the above MsgBox should be activated.

 

[ManiacB]

Try it this way

Private Sub TextBox1_Change()
With ThisWorkbook.ActiveSheet
With .Range("D1", .Cells(.Rows.Count, "D").End(xlUp))
If InStr(UCase(Me.TextBox1.Value), "/") > 0 Or InStr(UCase(Me.TextBox1.Value), ",") > 0 Then
MsgBox("Please confirm", vbYesNo, "Double Check") = vbAbort
End If
End Sub

 

[Akw47]

Try it this way

Private Sub TextBox1_Change()
With ThisWorkbook.ActiveSheet
With .Range("D1", .Cells(.Rows.Count, "D").End(xlUp))
If InStr(UCase(Me.TextBox1.Value), "/") > 0 Or InStr(UCase(Me.TextBox1.Value), ",") > 0 Then
MsgBox("Please confirm", vbYesNo, "Double Check") = vbAbort
End If
End Sub

There seems to be a compile error in the code above

 

[ManiacB]

Let's try removing the Ucase command....

Private Sub TextBox1_Change()
With ThisWorkbook.ActiveSheet
With .Range("D1", .Cells(.Rows.Count, "D").End(xlUp))
If InStr(Me.TextBox1.Value, "/") > 0 Or InStr(Me.TextBox1.Value, ",") > 0 Then
    MsgBox("Please confirm", vbYesNo, "Double Check") = vbAbort
End If
End Sub

 

[GWteB]

Due to the "= vbAbort" part VBE refuses to compile, so drop it ...

MsgBox("Please confirm", vbYesNo, "Double Check") = vbAbort

 

[Akw47]

Due to the "= vbAbort" part VBE refuses to compile, so drop it ...

MsgBox("Please confirm", vbYesNo, "Double Check") = vbAbort

Hi, now there seems to be a syntax error in the code.

 

[Akw47]

I actually kind of solved the error. But now my code is such that I need to type "12345 ," in textbox.1 for the MsgBox to pop out.
Instead I want to be typing 12345, if there is a "," or "/" in cell. MsgBox will pop out.

Private Sub TextBox1_Change()
Dim m As String

With ThisWorkbook.ActiveSheet
With .Range("D1", .Cells(.rows.Count, "D").End(xlUp))
If (InStr(Me.TextBox1.Value, "/") > 0 Or InStr(Me.TextBox1.Value, ",") > 0) Then
m = MsgBox("Please confirm", vbYesNo, "Double Check")
        End If
    End With
End With
End Sub

 

[ManiacB]

I'm stumped, too. Unless there is a space with the comma in the code, it doesn't make sense

 

[Akw47]

I'm stumped, too. Unless there is a space with the comma in the code, it doesn't make sense

thanks for the help anyway

 

[GWteB]

I feel like you're looking for something like this ...

Private Sub TextBox1_Change()
    Dim c As Range
    With ThisWorkbook.ActiveSheet
        For Each c In .Range("D1", .Cells(.Rows.Count, "D").End(xlUp))
            If InStr(c, "/") > 0 Or InStr(c, ",") > 0 Then
                If MsgBox("Please confirm cell " & c.Address, vbYesNo, "Double Check") = vbNo Then Exit Sub
            End If
        Next c
    End With
End Sub