• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto complete string or number

K

krishnabsharma

New Member
Hi,
Greetings there!

Trying to create an application in excel VBA for maintaining few basic employee data points. Stuck somewhere and need help.

Problem Statement - Have EmpID, EmpName and Department as the textbox fields in form.
  1. Whenever any employee start key-in his EmpID in id textbox, textbix should provide all the available suggestions to choose (just like Google search)
  2. Once EMP ID selected in textbox from available suggestions, then name and department of that particular emp should get populated in relevant field
  3. Also can above both logic be built for other 2 fields as well (Name and Dept)? Meaning suggestion/auto complete for other 2 fields and populate remaining based on selection?
 

Attachments

  • AutoComplete_String_Number.xlsx
    9.7 KB · Views: 8
Hi, krishnabsharma!
You've uploaded a file with only a worksheet containing a 3 columns table with sample information. You mention a textbox and tied fields for name and department too. But you didn't provide any clue regarding the implementation, if in worksheets, in user forms, no layouts, ...
Could you please elaborate and upload a sample file (including manual examples of desired output if applicable) and the complete layout of worksheets/userforms involved? It'd be very useful for those who read this and might be able to help you. Thank you.
Regards!
 
Hi SirBJ7,

Thanks for your revert :) and sorry as i forgot to mention that part in my first post.

Here are the details -
  1. This file will be published at particular shared drive or network. Individual employee of the org will open this file and a form will be populated when file gets opened
  2. Employee will start key-in the info, e.g. - EMP ID, which will be searched in base data sheet and relevant suggestions will be given to select from. Once EMP ID selection done, other 2 fields will be auto-populated
  3. After completion of all data key-i/selection, user will click on submit button, and data will get saved in another sheet, say EMP_Details
Post these steps, i will be doing some analysis, like creating pivots, charts and other stuff.

Hope this is clears the requirement.

Regards,
Krishna
 

Attachments

  • AutoComplete_String_Number.xlsm
    15.5 KB · Views: 9
Hi, krishnabsharma!

A bit clearer but still gray, more towards black than nearly white.

A first list of doubts:
a) Where's the source data? Worksheet? That which will be used to search into and used for suggestions/retrievals.
b) When opened the file, the userform should be displayed?
c) When the userform is displayed, which is the process to be done? The user enters something in ID, some information should be suggested, which?, criteria?, how?
d) When the user inputs or selects an entry from the suggested info, what will be done with that record? It should be copied, written, marked, what and where?
e) How does the cycle start again?

Regards!
 
Hi SirBJ7,

PSB my comments please -
  1. Source data - For all the controls used in user-form, data will be available in "Master" sheet
  2. Yes, whenever user opens this file, a user-form will be opened
  3. Selection Process - There will only be one criteria as whenever user key-in any data in relevant field/s, suggestions should be listed to the user to choose data
  4. What next post data selection - Once user selects all data, the selected data should get stored in the sheet "EMP_Details" as given in attached worksheet
  5. This activity will be weekly basis, and will be completely manual to start
Hope i have cleared doubts.
 

Attachments

  • AutoComplete_String_Number.xlsm
    15.5 KB · Views: 6
Hi Sharma ,

Please wait for one more day ; in case you don't get any help , I'll do it , provided it is not urgent.

Narayan
 
Hi Krishna,

I am quite sure, Sir JB7 and Narayan can give you a more concrete coding/answer than what I have below...
But just in case this is urgent you can try doing the below...

- Add a button to check details..
- Once the Employee enters his Emp ID in the first textbox then he/she will hit the check details tab (newly created in the userform) once this is button is hit then the macro will pick the textbox1.value and then find it in your master database..
- Then use activecell.offset(0,1).value and activecell.offset(0,2).value to pick up the values for Emp Name and Dept and then fill the two textboxes using these values..

Hope this helps...:)
 
Hi, krishnabsharma!
I apologize as I have forgotten all about this topic, somehow it slipped from my pending list. Worse indeed I won't be able to work on it until Monday. Sorry for the inconvenience.
Regards!
 
Hi this file was modified in excel 2003, but may be of some help. It may need some fine tuning, refining.
 

Attachments

  • Autocompletestring.xls
    51.5 KB · Views: 20
Hi, krishnabsharm!
The forums software doesn't have a closed/solved feature, so your previous comment will do the job for people who might read this topic later.
Regards!
 
You must log in or register to reply here.
Back
Top