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
================================================== ========
--
PeteCresswell