Solved

Need Help implementing autocomplete on data validation drop down

Posted on 2013-10-29
5
475 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Limit the # times a macro code will run 14 50
VBA: loop recent folder and copy txt file. 8 33
Date Formatting on Userform Print 5 26
V-Lookup 11 17
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
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.

839 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