none
Adjust Validation list RRS feed

  • Question

  • Hi,

    Is there a way to adjust the Validation list of the cell, programmably, during run-time? For instance, I expect to adjust the Validation list of Cell B, due to different value on Cell A.


    Many Thanks & Best Regards, Hua Min

    Thursday, March 10, 2016 1:44 AM

Answers

  • Hi HuaMin,

    >> Is there a way to adjust the Validation list of the cell, programmably, during run-time?

    Yes, you could change Validation, here is a simple code, you could modify it as your own requirement and developer language.

        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=$B$2:$B$6"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .IMEMode = xlIMEModeNoControl
            .ShowInput = True
            .ShowError = True
        End With

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by Jackson_1990 Friday, March 11, 2016 7:33 AM
    Friday, March 11, 2016 7:21 AM

All replies

  • Not sure if it is what you are looking for but have a look at the following link.

    http://blog.contextures.com/archives/2013/09/26/conditional-drop-down-lists-in-excel/


    Regards, OssieMac

    Thursday, March 10, 2016 5:04 AM
  • Within this Excel file, per given value from Column A, there're different Validation list shown in Column B. Moreover, per given value from Column B, there're different Validation list shown in Column C. Now within Cells D2-52, I am to choose the validation list (that is for Column B). How to apply dependent Lookup on Column E and F?

    To my Excel file in above, I need to 2 other dependent validation list on Column E, F. If I have selected Hardware purchase on column D, then within Column E, I only can choose those topic that is under Hardware purchase. 

    If I have selected 'PC' on column E, then on column F, I only can choose those Item codes that are under Topic PC.


    Many Thanks & Best Regards, Hua Min



    Thursday, March 10, 2016 6:38 AM
  • Hi HuaMin,

    >> Is there a way to adjust the Validation list of the cell, programmably, during run-time?

    Yes, you could change Validation, here is a simple code, you could modify it as your own requirement and developer language.

        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=$B$2:$B$6"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .IMEMode = xlIMEModeNoControl
            .ShowInput = True
            .ShowError = True
        End With

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by Jackson_1990 Friday, March 11, 2016 7:33 AM
    Friday, March 11, 2016 7:21 AM