Matching Number in Textbox

[Akw47]

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.

 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

 

[Yongle]

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]

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]

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]

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]

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]

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)

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]

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)

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]

i wouldn't use the Change event, but the AfterUpdate

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]

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)