Applying Validation

  • I'm trying to apply validation using a dynamic list from sheet4 to a range on sheet10, but the following code fails at the ".Add" line? I suspect it might be looking for a string not a range. Anyone able to explain why, and how to fix it?


  • Re: Applying Validation


    Hello,


    You should make a tiny modification ...


    Code
    Formula1:="=" & rList.Address


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Applying Validation


    This worked perfectly for me with Excel 2010


    Make sure the file you are using has the 2 required sheets with code names Sheet4 and Sheet10, or change the code to suit your actual sheet code names.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Applying Validation


    I worked it out. It was failing because there was already validation in place. The following code fixed it:

  • Re: Applying Validation


    Made a change in the code to ensure it grabs any new values for the list:


    Unfortunately, it's not grabbing the correct values (I think it's grabbing the values from Sheet10 where the code is run from). Then I have a brainwavem and decided to use a dynaic named range for the list values:



    This works well, except for some reason the named range is including a blank value at the bottom, and as validation works, of there's a blank it automatically goes to this when the drop-down list is used.


    So... any ideas how to ensure the dynamic named range does not include blanks, and/or why the dynamic range in the first pice of code isn't grabbing the correct values?

  • Re: Applying Validation


    Answered my own question regarding the dynamic named range solution. I was using row 2 as the start of the dynamic named range, when I changed it to row 1, it stopped populating a blank.


    I'm still curious why the dynamic range code doesn't work...

  • Re: Applying Validation


    Quote from ryangus;795466

    I'm still curious why the dynamic range code doesn't work...


    Hello again,


    Would you mind posting the formula you are using for your dynamic named range ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Applying Validation


    Here you go:

    Code
    =OFFSET(Lists!$T$1,0,0,MATCH("*",Lists!$T:$T,-1),1)

    used like this:

  • Re: Applying Validation


    Hello,


    Your Data Validation Offset formula is fine ...


    In order to use it in your macro ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!