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

Joe Derr · Mar 16th 2011

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??

Code

Row = ActiveCell.Row
Range("N" & ActiveCell.Row).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=INDIRECT(M" & Row & ")"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Display More

GlennUK · Mar 16th 2011

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?

Joe Derr · Mar 16th 2011

Re: Data Validation

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

Column M has a Formula

Code

=IF(ISTEXT(R31),(R31),(IF(ISERR(W31),(""),(CONCATENATE(R31,"",W31)))))

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..

GlennUK · Mar 16th 2011

Re: Data Validation

Once you've set one up you should be able to copy it where needed.

ShosMeister · Mar 16th 2011

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?

Joe Derr · Mar 16th 2011

Re: Data Validation

ShosMeister,

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..

Windy58 · Mar 16th 2011

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.

Code

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

For example select cells c3:cx run the macro.

Windy

AAE · Mar 16th 2011

Re: Data Validation

Joe,

"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.

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

Participate now!

Share