Adding Validation When Source Validates To Error?

[(PeteCresswell)]

When I add validation to a cell manually - using Excel's UI -
Excel issues a "The Source currently evaluates to an error. Do
you wish to continue dialog". I click "Yes" and all is well.

But when I try to add that same validation via VBA code from an
MS Access app, it traps out with "1004: Application-defined or
object-defined error"

It's the second validation in the code below. No problem
with the first one.

I'm assuming it's the "...source currently evaluates to an
error..." thing that's provoking the 1004.

If that's correct, is there a way around it?

Maybe something like an Excel equivalent of
DoCmd.SetWarnings=False that can be issued against the
Excel.Application object?

VBA Code:
========================================================== '
---------------------------------------------------
' Set up validation

2450 With myWS
2460 Set myRange = Range(.Cells(1, 1), .Cells(6, 1))
2461 With myRange.Validation
2462 .Delete
2463 .Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:=xlBetween,
Formula1:="=DealNames" <===this one works, no problem
2464 .IgnoreBlank = True
2465 .InCellDropdown = True
2469 End With

2470 Set myRange = Range(.Cells(1, 2), .Cells(6, 2))
2471 With myRange.Validation
2472 .Delete
2473 .Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:=xlBetween,
Formula1:="=INDIRECT(RC[-1])" '<==this one traps out w/1004
2474 .IgnoreBlank = True
2475 .InCellDropdown = True
2479 End With
2499 End With
==========================================================

 

[Tom Ogilvy]

myWS.Application.DisplayAlerts = False

 

[(PeteCresswell)]

Per Tom Ogilvy:

myWS.Application.DisplayAlerts = False

Seems to have the desired effect if/when I jump over to the
worksheet and manually apply Formula1:="=INDIRECT(RC[-1])"

But the code still traps out with a 1004 on line 2474.
viz:
==================================================
2470 Set myRange = Range(.Cells(1, 2), .Cells(6, 2))
2471 mySS.Application.DisplayAlerts = False 'BC this box's
validation validates to an error since it is intially blank
2472 With myRange.Validation
2473 .Delete
2474 .Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:=xlBetween,
Formula1:="=INDIRECT(RC[-1])"
2475 .IgnoreBlank = True
2476 .InCellDropdown = True
2477 End With
2479 mySS.Application.DisplayAlerts = False
2499 End With

==================================================

Maybe I'm jumping to cause with the "..evaluates to an error"
explaination and doing something else that's
provoking the 1004?

 

[Tom Ogilvy]

As I recall,
Relative references in Data Validation are relative to the ActiveCell where
ever that may be. I don't know what you are doing, but maybe select the cell
with the data validation before applying it. Also, perhaps you need to at
least temporarily populate the cell references to insure the formula does not
evaluate to an error.

--
Regards,
Tom Ogilvy

Per Tom Ogilvy:

myWS.Application.DisplayAlerts = False

Seems to have the desired effect if/when I jump over to the
worksheet and manually apply Formula1:="=INDIRECT(RC[-1])"

But the code still traps out with a 1004 on line 2474.
viz:
==================================================
2470 Set myRange = Range(.Cells(1, 2), .Cells(6, 2))
2471 mySS.Application.DisplayAlerts = False 'BC this box's
validation validates to an error since it is intially blank
2472 With myRange.Validation
2473 .Delete
2474 .Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:=xlBetween,
Formula1:="=INDIRECT(RC[-1])"
2475 .IgnoreBlank = True
2476 .InCellDropdown = True
2477 End With
2479 mySS.Application.DisplayAlerts = False
2499 End With

==================================================

Maybe I'm jumping to cause with the "..evaluates to an error"
explaination and doing something else that's
provoking the 1004?

 

[(PeteCresswell)]

Per Tom Ogilvy:

Also, perhaps you need to at
least temporarily populate the cell references to insure the formula does not
evaluate to an error.

That's the route I took. Created a bogus entry at the end of
each lookup table, populated with just a single underscore -
which is legal for a range name.

After .Adding the .Validation, I went back and removed the
underscores. Prolly ought to delete the range named "_" and
adjust the dimensions of the ranges to ignore the dummy rows...
but for now, I'm just gonna move on to the vLookup part...