Solved

Need Help implementing autocomplete on data validation drop down

Posted on 2013-10-29
5
484 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Database 5 47
Merging text files strings with filename 18 46
Excel 2016 lost MRU list 8 70
How to always round a decimal up 5 22
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

733 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