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.
 

Akw47

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
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
Yep something like that, however, the msgbox keep pooping out after typing 1 digit in. My data is usually 5 digits long.
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,090
Office Version
  1. 2013
Platform
  1. Windows
How about ...
Rich (BB code):
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

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
How about ...
Rich (BB code):
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

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

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

ADVERTISEMENT

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
Rich (BB code):
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 ...

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

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

Well-known Member
Joined
May 3, 2019
Messages
846
Office Version
  1. 2010

ADVERTISEMENT

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

Well-known Member
Joined
Sep 10, 2010
Messages
1,090
Office Version
  1. 2013
Platform
  1. Windows
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 ...

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

Akw47

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
See if this works for you ...

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

Well-known Member
Joined
Sep 10, 2010
Messages
1,090
Office Version
  1. 2013
Platform
  1. Windows
You are welcome, glad it's sorted.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,797
Messages
5,542,558
Members
410,560
Latest member
1ndependent
Top