Message Box after a condition is met

Akw47

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

ManiacB

Board Regular
Joined
Aug 11, 2020
Messages
50
Office Version
  1. 365
Platform
  1. Windows
Try it this way

VBA Code:
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

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Try it this way

VBA Code:
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

Board Regular
Joined
Aug 11, 2020
Messages
50
Office Version
  1. 365
Platform
  1. Windows
Let's try removing the Ucase command....

VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,090
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Due to the "= vbAbort" part VBE refuses to compile, so drop it ...
Rich (BB code):
MsgBox("Please confirm", vbYesNo, "Double Check") = vbAbort
 

Akw47

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Due to the "= vbAbort" part VBE refuses to compile, so drop it ...
Rich (BB code):
MsgBox("Please confirm", vbYesNo, "Double Check") = vbAbort
Hi, now there seems to be a syntax error in the code.
 

Akw47

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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.

VBA Code:
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

Board Regular
Joined
Aug 11, 2020
Messages
50
Office Version
  1. 365
Platform
  1. Windows
I'm stumped, too. Unless there is a space with the comma in the code, it doesn't make sense
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,090
Office Version
  1. 2013
Platform
  1. Windows
I feel like you're looking for something like this ...

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,890
Messages
5,544,887
Members
410,643
Latest member
sng
Top