Need Help implementing autocomplete on data validation drop down

Hello,

In the attached workbook, I have two worksheets.  Worksheet non combo employs a straight data validation on the payment type column. If you enter 8 hours in the hours worked column, the corresponding payment type is set to R.  You can then select a different payment type via the data validation drop-down list.  The selection is then processed by the Call basExcelUtilities.SetPaymentTypeCode procedure in the worksheet change event which then retrieves the correct code to populate the payment type cell.  However, the customer wants me to employe auto completion in the data validation cells.  I did some research and found code at http://www.contextures.com/xlDataVal11.html that would allow me to do that.  The code does partially work in that the selected value from the combo box is displayed in the cell.  I'm trying to figure out how to replace the displayed value with its code via thebasExcelUtilities.SetPaymentTypeCode(Target) procedure. This can be seen in the worksheet WTS_Combo.  I've tried placing the call to that procedure in the Worksheet_SelectionChange event but it's still not working the way I want.
Timesheet-v1-23.xlsm
chtullu135Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
[ fanpages ]Connect With a Mentor IT Services ConsultantCommented:
The creation of ActiveX controls can be as dynamic as the addition of Data Validation to subsequent cells but if you had mentioned reservations in this respect originally I would not have made the suggestion.

Yes, I appreciate the actual payment code is being used; my point was that, I presume, you (or the users) would wish to see the code and the expanded description, rather than just the code.

Either way, it doesn't sound like you wish to pursue this option, so I will let somebody else advise accordingly.
0
 
[ fanpages ]Connect With a Mentor IT Services ConsultantCommented:
Hi,

Have you considered using ActiveX Combo Box controls instead?

The "auto-complete" feature would then be implemented as standard.

I have updated your workbook to incorporate seven Combo Box controls in column [H].

I have had to remove the "Microsoft Calendar Control 2007" reference from the VBAProject, however, as this is not available to me at present (on the machine I am typing from).

Note that your existing code that is called when entering any value into the corresponding "Hours Worked" column will need to also populate the "Payment Type" cell with the full description (rather than just the initial letter of the code).

Also, the background colo(u)r of the seven Combo Boxes may need to be changed if 0 is recorded as the "Hours Worked" to be consistent with your existing implementation.

BFN,

fp.
Q-28280639.xlsm
0
 
chtullu135Author Commented:
Hello BFN,

I cannot use Active x Box controls since the number of rows will be dynamic.  For instance, there can be multiple entries for the same date, having different payment types.  As for the the initial letter of the code, what is being entered, upon entering the hours is the actual payment code.
0
 
chtullu135Author Commented:
Hello fanpages.

Actually the user only needs to see the description during the selection process.  The reason that only the code is stored is because the data on the worksheet will be exported via a csv file to another application (Spectrum) whose file input requirements are that only the codes will be imported
0
 
chtullu135Author Commented:
Thanks for the advice
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.