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
 

Akw47

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
wow mike, yours work as well. However, where should I add the count if the number match with the column.
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Akw47

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
wow mike, yours work as well. However, where should I add the count if the number match with the column.
"count if" e.g WorksheetFunction.CountIf(Range("B:B"), TextBox1) > 0
 

Akw47

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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)
I understand now. Thanks for your help yongle :)
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,804
VBA Code:
Private Sub TextBox1_AfterUpdate()
    If TextBox1.Text = TextBox2.Tag And TextBox1.Text <> vbNullStringThen
        TextBox2.Text = Val(TextBox2.Text) + 1
    Else
        If IsNumeric(Application.Match(TextBox1.Text, Range("B:B"), 0)) Then
            TextBox2.Text = "1"
            TextBox2.Tag = TextBox1.Text
        Else
            TextBox2.Tag = vbNullString
            TextBox2.Text = "not"
        End If
    End If
End Sub
 

Akw47

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

ADVERTISEMENT

VBA Code:
Private Sub TextBox1_AfterUpdate()
    If TextBox1.Text = TextBox2.Tag And TextBox1.Text <> vbNullStringThen
        TextBox2.Text = Val(TextBox2.Text) + 1
    Else
        If IsNumeric(Application.Match(TextBox1.Text, Range("B:B"), 0)) Then
            TextBox2.Text = "1"
            TextBox2.Tag = TextBox1.Text
        Else
            TextBox2.Tag = vbNullString
            TextBox2.Text = "not"
        End If
    End If
End Sub
seems to be a error with
VBA Code:
If TextBox1.Text = TextBox2.Tag And TextBox1.Text <> vbNullStringThen
 

Akw47

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

ADVERTISEMENT

Yes there is. A space between String and Then.
Hi mike, did the changes, and it still can't perform the finding function, of the column B. It keeps getting "not" even when the number matches with what is in one of the cells.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,871
Office Version
  1. 365
Platform
  1. Windows
However, I realised that the number of times the data matched is not saved. E.g after typing 12345 twice, it pops out 2 in textbox.2 However, after typing another number like 23456 and back to 12345, textbox.2 starts from 1 again. Is it possible to fix that? thankss

try this
VBA Code:
Option Explicit
Private aDict As Object
Private Sub TextBox1_Change()
    Dim T As String
    If Len(TextBox1) < 5 Then Exit Sub
    T = TextBox1
    TextBox1 = ""
    TextBox2 = ""
    If aDict Is Nothing Then Set aDict = CreateObject("Scripting.Dictionary")
    If WorksheetFunction.CountIf(Range("B:B"), T) > 0 Then
        If aDict.Exists(T) Then aDict(T) = aDict(T) + 1 Else aDict.Add T, 1
        TextBox2 = Right(aDict(T), 5)
    End If
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
 
Solution

Akw47

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
try this
VBA Code:
Option Explicit
Private aDict As Object
Private Sub TextBox1_Change()
    Dim T As String
    If Len(TextBox1) < 5 Then Exit Sub
    T = TextBox1
    TextBox1 = ""
    TextBox2 = ""
    If aDict Is Nothing Then Set aDict = CreateObject("Scripting.Dictionary")
    If WorksheetFunction.CountIf(Range("B:B"), T) > 0 Then
        If aDict.Exists(T) Then aDict(T) = aDict(T) + 1 Else aDict.Add T, 1
        TextBox2 = Right(aDict(T), 5)
    End If
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 it! you got me. Thanks for your help. Could you explan to me about the aDict function that you used?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,871
Office Version
  1. 365
Platform
  1. Windows
Could you explan to me about the aDict function that you used?

A VBA dictionary is used to store a "list of items" and associated "values"

In the code above ... aDict stores a "list of 5 digit numbers" and associated "count"
The dictionary is checked when a 5 digit number is entered in textbox1
... numbers not in the dictionary are inserted and given the value 1
... otherwise the value is increased by 1

So aDict looks like this
12345 2
12346 1
12349 5

For fuller explanation of dictionaries:
 

Watch MrExcel Video

Forum statistics

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