Lookup ID number (format number) in a textbox - Excel VBA

[PrimaSatria]

Lookup ID number (format number) in a textbox - Excel VBA
Hi,

How the best way to store ID number format, is it text or number?
I am lookup the ID number to get name, department, etc. I found no problem if the ID number is in text format, but when I changed the format into number nothing happen.

Below the detail:
ComboBox8 = ID Number
TextBox20 = Name
TextBox21 = Department
Sheets("Employ") = data source
Column F = ID Number
Column G = Name
Column H = Department

I use AfterUpdate_event as below:

Please Login or Register  to view this content.

How to fix the code above so I can do lookup even if ID Number is in number format?

Thank you

[jindon]

https://www.excelforum.com/excel-pro...excel-vba.html

Please Login or Register  to view this content.

[PrimaSatria]

Dear jindon,

I used your code. It works. Your code used to call/extract the last loan number. The current problem is the code for vlookup the name of employee and department based on ID Number. The name of employee and department is showing up if the format is text, but if the format of ID Number is number then it gave me nothing.

Thank you

[PrimaSatria]

Dear jindon,

Below the code:

Please Login or Register  to view this content.

Best regards

[jindon]

Upload you workbook, so it will be clearer.

[jindon]

I used your code. It works. Your code used to call/extract the last loan number. The current problem is the code for vlookup the name of employee and department based on ID Number. The name of employee and department is showing up if the format is text, but if the format of ID Number is number then it gave me nothing.

Thank you

Did you even test the code I posted?

[PrimaSatria]

Dear jindon,

I used it, and apply your code. It works so fine. I just had another problem to get Employee's name and Department if change the ID Number format from text to number. But that is another subject not related with extracting the last loan number.

So, when change or select an ID number:
- It will tells me the last number of loan based on each ID number (it worked as the code gave by you)
- tells me Name and Department with Vlookup (the code I wrote myself)

Please Login or Register  to view this content.

This lookup above is working only if ID Number is in text format, but it will show nothing if I changed the ID Number into number format.

Thank you

[PrimaSatria]

Dear Jindon,

Below is the complete code

Please Login or Register  to view this content.

It is written in Bahasa Indonesia:
Nama = Name
Departemen = Department
Alamat = Address
Pinjaman = Loan

lookup.png

Best regards

[PrimaSatria]

Dear Jindon,

Additional information:
- Nomor Pinjaman Terakhir = Last Loan Number
- Nomor Induk Karyawan = Employee's ID Number
- Jumlah Pinjaman Terakhir = Last Loan Amount

Best regards

[jindon]

Picture doesn't help.

Upload you workbook, so it will be clearer.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[PrimaSatria]

Dear jindon,

Please find the file.

Best regards

[jindon]

Still not clear about the column.
Try change the column letter(s)

Please Login or Register  to view this content.

[jindon]

How to fix the code above so I can do lookup even if ID Number is in number format?

I've missed this part
If you want it

Please Login or Register  to view this content.

[PrimaSatria]

Dear jindon,

I just want to lookup Nama (TextBox3) & Departemen (TextBox30) based on ComboBox8.value (Nomor Induk Karyawan).
The code I have is not working since the format of ID Number (in sheets "anggota") is number not text format.

Please Login or Register  to view this content.

jindon.png

Sorry for disturbing you and I really appreciate for your time, attention. You helping me a lot.

Thanks so much.

[jindon]

See if this is what you wanted...

Please Login or Register  to view this content.

[daboho]

Keinginan anda bagaimana

[jindon]

PrimaSatria,

I suggest you to remove all On Error Resume statement from every procedure.
As the code updates textboxes, Update event code for the text boxes run with On Error statement.

You can not find what went wrong when error is ignored.

[PrimaSatria]

Dear jindon,

I've tried the last code, the problem still the same. Nothing showing up in TextBox30, TextBox3 and TextBox15.
Perhaps I will change the format of data source (ID Number) in column F sheets "anggota", from number into text

Thank you

[jindon]

OK, delete ComboBox8_AfterUpdate codes and change ComboBox8_Change code to

Please Login or Register  to view this content.

[daboho]

Please Login or Register  to view this content.

after i am not run this code bellow
then combobox8-Change run is well

Please Login or Register  to view this content.

[jindon]

daboho

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

2)
PrimaSatria,

Further Error trapping

Please Login or Register  to view this content.