Solved

Need Help implementing autocomplete on data validation drop down

Posted on 2013-10-29
5
445 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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

708 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

12 Experts available now in Live!

Get 1:1 Help Now