Excel 2010 VBA Assigned Data Validation Fails

Question

I have a worksheet used for data entry, a kind of sandbox tool to plan work. So the data set grows and shrinks as the work is planned and the plan is reworked, and optimized. The user often enters a list of tasks, then reorders the list, the inserts tasks in the middle of the list, and I need to add some validations to account for those moves. My solution was to set the data validation in the first cell in the column and copy and paste just the validation down the data set each time a row is added.

I am attempting to used data validation to force identical tasks to have the same duration. When I hand enter the data validation it works, and I've recorded a macro to double check my code. I've also tried replacing the formula with a variable defined as a string. But I can't get VBA to assign the data validation.

I am calling the following Sub when some of those moves occur. But I am getting a Run-time error 1004 on the .Add line. Doesn't like the formula or one of the parameters.

Sub Validate_Dur()

        Dim Last_Row As Long


        Last_Row = Sheets("Template").Cells(Rows.Count, 2).End(xlUp).Row ' catches the new last row
        Range("D3").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateCustom, _ ' this is where I'm having trouble
        AlertStyle:=xlValidAlertStop, _
        Formula1:="=AND(B3=B2,D3=D2)"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = "Duration Error"
        .InputMessage = ""
        .ErrorMessage = _
        "Tasks with identical descriptions must have the same duration."
        .ShowInput = True
        .ShowError = True
    End With
    Selection.Copy
    Range("D3" & Last_Row).Select
    Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub

Any help?

Paaqua Grant · Accepted Answer · 2016-06-02 19:02:10Z

0

There are a couple of problems (in addition to the one your question specifically addresses) with your code.

1) while this will apply the data validation rules to a cell, as written it will not apply it to the cell you want it on ("D3" & Last_Row) will append the value of Last_Row to "D3", so if Last_Row were 6, this would paste the validation to "D36", not the range "D3:D6" as I think is your intent.

2) after correcting the first problem, you will still run into the fact that any entries that already exist and do not meet these criteria will not immediately be flagged. Only when someone attempts to change a duration to an invalid entry will they receive the notification message box.

3) Your data validation rule, as written, will also prevent users from entering a duration which differs from the line above when the tasks are NOT the same. You can see this by applying that validation rule, and then clicking the "Circle Invalid Data".

Now, assuming that you can overcome problems 2 and 3 on your own, here is a simplified version that works on my system (Win7, Excel 2010-32bit):

Sub Validate_Dur()

Dim Last_Row As Long
Dim rngToValidate As Range

Set rngToValidate = ActiveSheet.UsedRange.Columns(4)
Set rngToValidate = rngToValidate.Offset(1, 0).Resize(rngToValidate.Rows.Count - 1)

With rngToValidate.Validation
    .Delete
    .Add xlValidateCustom, xlValidAlertStop, , "=AND(B2=B1,D2=D1)"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = "Duration Error"
    .InputMessage = ""
    .ErrorMessage = _
    "Tasks with identical descriptions must have the same duration."
    .ShowInput = True
    .ShowError = True
End With
ActiveSheet.CircleInvalid
End Sub

edited Jun 2, 2016 at 19:02

answered Jun 2, 2016 at 18:17

Paaqua Grant

1608 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sleepspeaking Over a year ago

well put, but the same run-time error occurs on the .Add line.

Ralph Over a year ago

I just posted your answer a few minutes ago with the same corrections (slightly differently solved) and deleted / withdrew the post one I got the same reply as you got just now. Maybe I should have left it...

Paaqua Grant Over a year ago

There must be something else going on with your system, as both my first answer, and my current edit work without any problems. Perhaps the problem is the timing of when it is being called by your other code, or possibly that this line is attempting to initiate an event that is interfering with its operation. Try adding Application.EnableEvents = False before the line where this sub is called (be sure to turn it back on afterwards).

Collectives™ on Stack Overflow

Excel 2010 VBA Assigned Data Validation Fails

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related