get the last 3 digit number of ID number of student

Bogz17

New Member
Joined
Feb 19, 2019
Messages
11
hi guys

i have a question about my userform i got 1 command button and 16 textbox.

the textbox 1 was the one that i use to search the data and display it to other textbox.


is it possible to textbox1 to search only the last 3 digit number instead of 8 digit? wish some can answer my question here.

thank you
:)
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Something like this:

Code:
Private Sub CommandButton1_Click()
    idnum = rigth(TextBox1.Value, 3)
    Set b = Sheets("sheet2").Columns("A").Find(idnum, lokat:=xlWhole, LookIn:=xlValues)
    If Not b Is Nothing Then
        'exists
    Else
        MsgBox "Does not exist"
    End If
End Sub
 
Upvote 0
idnum = rigth(TextBox1.Value, 3) ------------------>there was an error in here sir it said that sub or function not defined
Set b = Sheets("sheet2").Columns("A").Find(idnum, lokat:=xlWhole, LookIn:=xlValues)
If Not b Is Nothing Then
'exists
Else
MsgBox "Does not exist"
End If
 
Upvote 0
Sorry, try with this:

Code:
Private Sub CommandButton1_Click()
    Dim idnum As String, b As Object
    idnum = Right(TextBox1.Value, 3)
    Set b = Sheets("sheet2").Columns("A").Find(idnum, lookat:=xlWhole, LookIn:=xlValues)
    If Not b Is Nothing Then
        'exists
    Else
        MsgBox "Does not exist"
    End If
End Sub
 
Upvote 0
here is the code for my search button sir



Dim totRows As Long, i As Long


totRows = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count


If TextBox1.Text = "" Then
MsgBox "Enter the LHID you wish to search!"
End If




For i = 2 To totRows


If Trim(Sheet1.Cells(i, 1)) <> Trim(TextBox1.Text) And i = totRows Then
MsgBox "LHID not found!"
End If


If Trim(Sheet1.Cells(i, 1)) = Trim(TextBox1.Text) Then


TextBox1.Text = Sheet1.Cells(i, 1)
TextBox2.Text = Sheet1.Cells(i, 2)
TextBox3.Text = Sheet1.Cells(i, 3)
TextBox4.Text = Sheet1.Cells(i, 4)
TextBox5.Text = Sheet1.Cells(i, 5)
TextBox6.Text = Sheet1.Cells(i, 6)
TextBox7.Text = Sheet1.Cells(i, 7)
TextBox8.Text = Sheet1.Cells(i, 8)
TextBox9.Text = Sheet1.Cells(i, 9)
TextBox10.Text = Sheet1.Cells(i, 10)
TextBox11.Text = Sheet1.Cells(i, 11)
TextBox12.Text = Sheet1.Cells(i, 12)
TextBox13.Text = Sheet1.Cells(i, 13)
TextBox14.Text = Sheet1.Cells(i, 14)
TextBox15.Text = Sheet1.Cells(i, 15)
TextBox16.Text = Sheet1.Cells(i, 16)
Exit For
End If


Next i
 
Upvote 0
I made the changes based on your data

Code:
Private Sub CommandButton1_Click()
    Dim idnum As String, b As Object, i As Double
    idnum = Right(TextBox1.Value, 3)
    Set b = Sheet1.Columns("A").Find(idnum, lookat:=xlWhole, LookIn:=xlValues)
    If Not b Is Nothing Then
        'exists
        i = b.Row
        TextBox1.Text = Sheet1.Cells(i, 1)
        TextBox2.Text = Sheet1.Cells(i, 2)
        TextBox3.Text = Sheet1.Cells(i, 3)
        TextBox4.Text = Sheet1.Cells(i, 4)
        TextBox5.Text = Sheet1.Cells(i, 5)
        TextBox6.Text = Sheet1.Cells(i, 6)
        TextBox7.Text = Sheet1.Cells(i, 7)
        TextBox8.Text = Sheet1.Cells(i, 8)
        TextBox9.Text = Sheet1.Cells(i, 9)
        TextBox10.Text = Sheet1.Cells(i, 10)
        TextBox11.Text = Sheet1.Cells(i, 11)
        TextBox12.Text = Sheet1.Cells(i, 12)
        TextBox13.Text = Sheet1.Cells(i, 13)
        TextBox14.Text = Sheet1.Cells(i, 14)
        TextBox15.Text = Sheet1.Cells(i, 15)
        TextBox16.Text = Sheet1.Cells(i, 16)
    Else
        MsgBox "LHID not found", vbExclamation
    End If
End Sub
 
Upvote 0
thank you for your effort and time sir but it tells it cant find the data sir in the worksheet when i search the last 3 digit number..:(
 
Upvote 0
I assume you have numeric data in the cell, then try the following:

Code:
Private Sub CommandButton1_Click()
    Dim idnum As Variant, b As Object, i As Double
    idnum = Right(TextBox1.Value, 3)
[COLOR=#0000ff]    if isnumeric(idnum) then idnum = val(idnum)[/COLOR]
    Set b = Sheet1.Columns("A").Find(idnum, lookat:=xlWhole, LookIn:=xlValues)
    If Not b Is Nothing Then
        'exists
        i = b.Row
        TextBox1.Text = Sheet1.Cells(i, 1)
        TextBox2.Text = Sheet1.Cells(i, 2)
        TextBox3.Text = Sheet1.Cells(i, 3)
        TextBox4.Text = Sheet1.Cells(i, 4)
        TextBox5.Text = Sheet1.Cells(i, 5)
        TextBox6.Text = Sheet1.Cells(i, 6)
        TextBox7.Text = Sheet1.Cells(i, 7)
        TextBox8.Text = Sheet1.Cells(i, 8)
        TextBox9.Text = Sheet1.Cells(i, 9)
        TextBox10.Text = Sheet1.Cells(i, 10)
        TextBox11.Text = Sheet1.Cells(i, 11)
        TextBox12.Text = Sheet1.Cells(i, 12)
        TextBox13.Text = Sheet1.Cells(i, 13)
        TextBox14.Text = Sheet1.Cells(i, 14)
        TextBox15.Text = Sheet1.Cells(i, 15)
        TextBox16.Text = Sheet1.Cells(i, 16)
    Else
        MsgBox "LHID not found", vbExclamation
    End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,179
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top