Insert data validation into selected cell via macro short-cut key

  • Hi all...

    The code below is what I am trying to modify to insert a Data Validation, List, source is indirect to the next column back.

    This formula I am intending to hit CTRL SHIFT Z and it will grab the current row, and write the formula .. the reason is I have (150+) to insert this data validation and I would like to click the cell, chit Ctrl Shift Z and then select the next row..

    The problem is, I recorded the process using the Record Macro, and even *that* code errors.

    The error it gives me a "Run-time error '1004': Application-defined or object-defined error"

    Any ideas??

  • Re: Data Validation

    If your reference currently evaluates to an error your macro will crash. Can you put a valid entry into Range("N" & ActiveCell.Row), then do the validation, and then reset it afterwards?

    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

  • Re: Data Validation

    I am thinking this isn't going to be easy...

    Column M has a Formula


    Column R is Data Validated based on a List of options when one is selected populates Column M which in turns populates Column N... All I am trying to do is quickly put the code on to the sheet via Macro.. but because its so imbedded, it wont be possible it seems..

    All I am trying to do is using a Macro, input data validation based off of the current selected row..

    guess I will be putting in by hand..

  • Re: Data Validation

    Okay so now I'm confused.

    You have a formula in "M". Then you say after "R" is data validated, it updates "M" which in turn updates "N". So why do you have a formula in "M" to start with and what does it change to?

  • Re: Data Validation


    Believe me I know, it's confusing..

    I will see if I can somehow create an example workbook... As I said, I am just trying to insert a data validation quickly rather than, having to do all the clicking...

    I just deleted two paragraphs of how this workbook ties everything together..

    One updates the other, based on what it is.. the datavalidation changes to anyone of 7 data validation lists..

    Quickly... if the code in Column "N" is LL, in Column "M" it looks under the list for LL, which is 9 codes... If the code was GA in Column "N" then "M" would have 11 choices in the data validation...

    LOL... it's complicated... It's not what is in the other column or how it gets there.. I just wanted to put in the code via VBA.. sigh..

  • Re: Data Validation

    Hi Joe

    Could you not just select the column as far down as you need then add data validation?

    This macro will put data validation into selected range, just change A3 for your first cell location.

    Sub Macro1()
        With Selection.Validation
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=INDIRECT(SUBSTITUTE(A3,"" "",""""))"
        End With
    End Sub

    For example select cells c3:cx run the macro.


  • Re: Data Validation


    "Data Validation" as a thread title is far too generic and is of little help to others searching the forum.
    As a senior member of the forum with nearly 200 posts, you know the value of a well-worded thread title.

    Please send me a PM with a proper title that accurately describes this thread and it will be re-opened.

Participate now!

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