• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Setting cell value to first item in validation list: Sub Procedure needed

J

jayakum108

New Member
In "enterSourceType" cell, user will either say "Plant" or "Toller".


In next cell (enterPlant), user is provided a list of plants or tollers. So that cell has the validation source formula:


=IF(mySourceType = "Plant", allPlants, allTollers)


This works well.


Now I when user changes the enterSourceType selection, I also want the enterPlant entry, if not already an item in the appropriate list, to change to the first item in the appropriate list.


While I can do that in the Worsheet_Change function with VBA, I have a lot of cells like this that have their dropdown lists changing dynamically. So I want to write a Sub:


Sub ValidateAndResetCellEntry (cellname as Range)


' If cell value is not one of the items in the validation list, set it equal to

' the first item in the validation list.


End Sub


Another reason for a Sub is that I need to "access" the actual list of options in the cell because some of the validation formulas are long with many conditions and I don't want to duplicate that logic in VBA.


I looked around the web a lot and did not succeed.


Any help will be appreciated.


Thanks,

Jay
 
Check out Debra's macro DV0041 from here:

http://www.contextures.com/excelfiles.html


Description sounds almost exactly what you're looking for.
 
Thanks Luke.


The link you pointed out is good, it helped me understand how to work with Validation Type, Validation Forumula, etc.


However, it solves only part of my problem. The example assumes that Validation "source" is a range name (like "Plants") that I can look up the first element of.


In my case, the Validation "Source" could be a complex formula such as:


=IF(AND(myShipDirect = "Yes", mySourceType = "Toller"), TRANSPOSE(OFFSET(allToller2CustomerModeMatrix, INDEX(allToller2CustomerModeValidation,1)-1,1,1,INDEX(allToller2CustomerModeValidation,2))), IF(AND(myShipDirect="Yes", mySourceType = "Plant"), OFFSET(allModeWts,1,0,ROWS(allModeWts)-1,1), TRANSPOSE(OFFSET(allRDCsModeCapability,INDEX(allRDCsModeCapabilityValidation,1)-1,1,1,INDEX(allRDCsModeCapabilityValidation,2)))))


This evaluates to a list of options - this list may not even be a named range - it could be a fraction of a named range. I need to know this "evaluated" list that is displayed as a list of options!


Am I being clearer now?


Thanks,

Timelessness
 
Could you define that formula as a Named Range, and then the macro would work. Would just need to be careful about keeping your relative and absolute references correct.
 
The problem is that there is a limit to the number of characters that can be typed in the Refers To box when defining a range name. So I cannot type the entire formula:


=IF(AND(myShipDirect = "Yes", mySourceType = "Toller"), TRANSPOSE(OFFSET(allToller2CustomerModeMatrix, INDEX(allToller2CustomerModeValidation,1)-1,1,1,INDEX(allToller2CustomerModeValidation,2))), IF(AND(myShipDirect="Yes", mySourceType = "Plant"), OFFSET(allModeWts,1,0,ROWS(allModeWts)-1,1), TRANSPOSE(OFFSET(allRDCsModeCapability,INDEX(allRDCsModeCapabilityValidation,1)-1,1,1,INDEX(allRDCsModeCapabilityValidation,2)))))
 
You must log in or register to reply here.
Back
Top