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.

 

[Akw47]

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

Yep something like that, however, the msgbox keep pooping out after typing 1 digit in. My data is usually 5 digits long.

 

[GWteB]

How about ...

Private Sub TextBox1_Change()
    Dim c As Range
    If Len(TextBox1.Value) >= 5 Then
        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 If
End Sub

 

[Akw47]

How about ...

Private Sub TextBox1_Change()
    Dim c As Range
    If Len(TextBox1.Value) >= 5 Then
        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 If
End Sub

u got it. but for c.Address can it not be $D$16 but D16 instead?

 

[Akw47]

I also want to check with you if it can match the number. E.g if i have 2 cell in column D with a "/" or a "," It should tell me with respect to the cell. Now it is telling me all the cell that contain "/" and ","

 

[GWteB]

but for c.Address can it not be $D$16 but D16 instead?

I don't understand this question. c.Address returns the first found cell within column D as per your requirements from your post #1.
The address is determined by your data. If your data looks different it might as well be D512.
If you don't want to see the dollar signs, you can do this by replacing this ...
If MsgBox("Please confirm cell " & c.Address, vbYesNo, "Double Check") = vbNo Then Exit Sub
with this

If MsgBox("Please confirm cell " & Replace(c.Address, "$", ""), vbYesNo, "Double Check") = vbNo Then Exit Sub

I also want to check with you if it can match the number. E.g if i have 2 cell in column D with a "/" or a "," It should tell me with respect to the cell. Now it is telling me all the cell that contain "/" and ","

In that case I would advise to drop the msgbox entirely and instead introduce a second text box on your userform to display the information you are looking for. This has the advantage that you do not have to cancel or confirm something every turn. If you agree, make sure there is a TextBox2 on your Userform, give it a height of aprox 75 and try the code below ...

Private Sub TextBox1_Change()
    Me.TextBox2.MultiLine = True
    Dim c As Range, s As String
    If Len(TextBox1.Value) > 0 Then
        With ThisWorkbook.ActiveSheet
            For Each c In .Range("D1", .Cells(.Rows.Count, "D").End(xlUp))
                If Left(c, Len(TextBox1.Value)) = TextBox1.Value Then
                    If InStr(c, "/") > 0 Or InStr(c, ",") > 0 Then
                        s = s & c.Value & vbTab & " in cell " & Replace(c.Address, "$", "") & vbNewLine
                    End If
                End If
            Next c
            TextBox2.Value = s
        End With
    Else
        TextBox2.Value = ""
    End If
End Sub

 

[Akw47]

Thanks for solving the $dollar signs GWteB but is it still possible to keep the msgbox function? bec I want to reduce the reading, but more of the yes or no function?

 

[abdelfattah]

hi, maniacb i test your code it works but before that as GWteB said you should delete vbabort also when delete bracket moreover you have typo in your code you forgot finish condition with twice it works akawa as i see it seems to set variable to work that what i note

​

 

[GWteB]

is it still possible to keep the msgbox function? bec I want to reduce the reading, but more of the yes or no function?

See if this works for you ...

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

 

[Akw47]

See if this works for you ...

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

Thanks. Did some minor changes, but it's all good now.

 

[GWteB]

You are welcome, glad it's sorted.