Matching Number in Textbox

Akw47

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

I come with a request. Hope all are doing fine.
So I have 1 textbox to type in numbers etc. 12345 (it will always be 5 digits).
It will have to match with the same number in the same colunm B.
VBA Code:
 With .Range("B1", .Cells(.rows.Count, "B").End(xlUp))
After matching, textbox 2 will show me it match 1 time.
Then textbox.1 immediately gets cleared and I typed in the same number 12345 again.
Could you help me make textbox 2 show me 2?

Thanks
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,871
Office Version
  1. 365
Platform
  1. Windows
What happens if a different number is entered in textbox1?
- what is the value in textbox2? 1 or 2?

Which textboxes are you using?
- are they active-X ?
 

Akw47

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
What happens if a different number is entered in textbox1?
- what is the value in textbox2? 1 or 2?

Which textboxes are you using?
- are they active-X ?
Yes, I am using active-X.

If a different number is entired in textbox1, it should search column B again. If it's the first time matching, textbox 2 should display 1.
So textbox 2 = num.of times matched
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,216
Office Version
  1. 365
Platform
  1. Windows
Cross posted Matching Number in TextBox

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,871
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

So ...
...you want to check if the number entered in text box1 appears in column B. If number is found then put 1 in textbox2
(Clear textbox1 before user uses textbox1 again)
Then user enters a number in textbox1. If that number is found in column B then put 1 in textbox2 unless the value in textbox1 is the same as the last time (before cleared) in which case put 2 in textbox2

Please confirm that you do not need to know how many times the value appears in column B

Does the value in textbox2 keep increasing if user enters same number in textbox1 repeatedly?

Please confirm that you did not cross post anywhere else so that I can continue to help you.
 

Akw47

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
So ...
...you want to check if the number entered in text box1 appears in column B. If number is found then put 1 in textbox2
(Clear textbox1 before user uses textbox1 again)
Then user enters a number in textbox1. If that number is found in column B then put 1 in textbox2 unless the value in textbox1 is the same as the last time (before cleared) in which case put 2 in textbox2

Please confirm that you do not need to know how many times the value appears in column B

Does the value in textbox2 keep increasing if user enters same number in textbox1 repeatedly?

Please confirm that you did not cross post anywhere else so that I can continue to help you.
1st statement : Yes
2nd statement : Yes
I do not need to know how many times the value appears in column B.
The value in textbox2 keeps increasing if user enters same number in textbox 1 repeatedly.
I did not cross post anywhere else other than the one above.
Please assist me if possible thanks.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,871
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The 2 procedures work together
Textbox1_KeyPress prevents user entering more than 5 numbers, also only allows numbers to be entered
(otherwise additional checks are required in Textbox1_Change)


VBA Code:
Private Sub TextBox1_Change()
    Dim txt2 As Long
    Static PrevValue As String
    If Len(TextBox1) < 5 Then Exit Sub
    If Len(TextBox2) = 0 Then TextBox2 = 0
    txt2 = CLng(TextBox2)
    If WorksheetFunction.CountIf(Range("B:B"), TextBox1) > 0 Then
        If TextBox1 = PrevValue Then txt2 = txt2 + 1 Else txt2 = 1
        TextBox2 = txt2
    Else
        TextBox2 = ""
    End If
    PrevValue = TextBox1
    TextBox1 = ""
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If Len(TextBox1) = 5 Then KeyAscii = 0
    Select Case KeyAscii
        Case 48 To 57
        Case Else: KeyAscii = 0
    End Select
End Sub
 

Akw47

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
The 2 procedures work together
Textbox1_KeyPress prevents user entering more than 5 numbers, also only allows numbers to be entered
(otherwise additional checks are required in Textbox1_Change)


VBA Code:
Private Sub TextBox1_Change()
    Dim txt2 As Long
    Static PrevValue As String
    If Len(TextBox1) < 5 Then Exit Sub
    If Len(TextBox2) = 0 Then TextBox2 = 0
    txt2 = CLng(TextBox2)
    If WorksheetFunction.CountIf(Range("B:B"), TextBox1) > 0 Then
        If TextBox1 = PrevValue Then txt2 = txt2 + 1 Else txt2 = 1
        TextBox2 = txt2
    Else
        TextBox2 = ""
    End If
    PrevValue = TextBox1
    TextBox1 = ""
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If Len(TextBox1) = 5 Then KeyAscii = 0
    Select Case KeyAscii
        Case 48 To 57
        Case Else: KeyAscii = 0
    End Select
End Sub
That's exactly what I needed. You legend. Btw are you free to teach me what KeyAscii means? If not nevermind :)
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,805
i wouldn't use the Change event, but the AfterUpdate
VBA Code:
Private Sub TextBox1_AfterUpdate()
    If TextBox1.Text = TextBox2.Tag Then
        TextBox2.Text = Val(TextBox2.Text) + 1
    Else
        TextBox2.Text = "1"
        TextBox2.Tag = TextBox1.Text
    End If
End Sub
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,871
Office Version
  1. 365
Platform
  1. Windows
are you free to teach me what KeyAscii means

Every character in Excel can be referred to by its Ascii code
examples
A -Z are 65 -to 90
a - z are 97 to 122
0 - 9 are 48 to 57 (used in the code above)

TextBox1_KeyPress is triggered by each keystroke and keyAscii is the Ascii value of that keystoke which we are able to use
The code above cancels the last keystroke if it is not a number

This formula placed in a cell returns the character number of the FIRST character in A1
=CODE(A1)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,209
Messages
5,546,552
Members
410,746
Latest member
Tcestnick
Top