Solved

Need Help implementing autocomplete on data validation drop down

Posted on 2013-10-29
5
459 Views
Last Modified: 2013-11-09
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
0
Comment
Question by:chtullu135
  • 3
  • 2
5 Comments
 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 500 total points
ID: 39611462
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
 

Author Comment

by:chtullu135
ID: 39611526
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
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 500 total points
ID: 39611778
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
 

Author Comment

by:chtullu135
ID: 39611928
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
 

Author Closing Comment

by:chtullu135
ID: 39635978
Thanks for the advice
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now