Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 11-13-2017, 08:49 AM
Phil H Phil H is offline Windows XP Office 2010 64bit
Advanced Beginner
 
Join Date: Jun 2010
Posts: 59
Phil H is on a distinguished road
Default Data Validation Using Choice/Definition Columns


Need a data validation macro for choice/definition:

......L ............M
5 Choice 1 Definition 1
6 Choice 2 Definition 2
7 Choice 3 Definition 3

When user invokes the macro (using the down arrow in the target cell), L5:M7 is displayed. User selects one of the L cells, which is populated in the target cell

For example: User clicks L6, target cell is populated with Choice 2.

The purpose is to put definitions in front of the user to insure accurate selection.
Reply With Quote
  #2  
Old 11-13-2017, 01:11 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Posts: 397
NoSparks is on a distinguished road
Default

Are you wanting assistance filling the validation drop down list or running a macro after the user selects from the drop down list or something else ?
What do you currently have towards this ?
Reply With Quote
  #3  
Old 11-14-2017, 05:33 AM
Phil H Phil H is offline Windows XP Office 2010 64bit
Advanced Beginner
 
Join Date: Jun 2010
Posts: 59
Phil H is on a distinguished road
Default

NoSparks, Thanks for your reply - appreciate your time.

The problem is the definition of each choice is fairly detailed and not readily discernible as to selection - but accuracy is important. Using DV, I can present the choices, but I want the user to see the definition as well.

Needed: when the user clicks a cell, a list of choices and their definitions appear (a ComboBox variation?). The choices and definitions (range) are located elsewhere in the worksheet. Clicking on a choice, that choice is populated in the cell. The choice/definition display disappears after the selection.

I presume this requires a macro, but maybe there is another way - don't know. VLookup?
Reply With Quote
  #4  
Old 11-14-2017, 02:07 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Posts: 397
NoSparks is on a distinguished road
Default

Quote:
the definition of each choice is fairly detailed and not readily discernible as to selection
this makes me think there might be too much to put in the second column of a combobox but you can always try.

Put an activeX combobox on the sheet. Set its properties ie: size, columncount, font size, listfillrange, etc.
Use a macro such as Worksheet_SelectionChange to set its visibile and location properties.
Then the comboboxes change event to write to the sheet and hide the combobox.

I've never had much success working with controls on a sheet and with data validation the font size is always an issue.

I'd probably use Worksheet_SelectionChange to bring up a user form that has the definitions easy to read along with option buttons or check boxes for the user to make their selection and then write back to the target cell.
Reply With Quote
  #5  
Old 11-16-2017, 04:31 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Advanced Beginner
 
Join Date: May 2017
Posts: 75
ArviLaanemets is on a distinguished road
Default

Do you need a list of possible selections in cell depending on some condition?

I myself use in such cases a pair of columns with data validation lists defined for them, where data validation list in 2nd column depends on selected value in 1st column.

For this I have a hidden sheet with a table where all possible selections for 1st column from data table are column headers, and in every column all possible selections for 2nd column from data table are listed. And then a dynamic named range is defined, which represents the range with possible selections from one column from hidden sheet, depending the selection in 1st column on data table (i.e. the named range is dynamic as it changes when you select another row in data table, and it is dynamic, as it selects different length ranges data from different columns on hidden sheet. The data validation list for 2nd column is set equal to defined dynamic range.

The table on hidden sheet can be filled manually, or it can be also dynamic, with columns calculated from other data in workbook.
Reply With Quote
Reply

Tags
data validation

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data Validation - List Webbn111 Excel 9 03-09-2016 04:30 PM
Update data connection - path, database name, query definition jaworski_m Excel 1 08-03-2015 02:37 AM
Paste over data validation saurabhlotankar Excel Programming 2 05-24-2015 11:52 PM
If/then formulas with data validation shira47 Excel 1 04-09-2012 05:14 AM
Data validation with IF Klyxrastafari Excel 1 10-03-2011 04:41 PM


All times are GMT -7. The time now is 06:51 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft