Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Validation When Source Validates To Error?
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation source = destination? | Excel Worksheet Functions | |||
How can I set up a validation list and source to another workshee | Excel Worksheet Functions | |||
Validation source field size and function. | Excel Discussion (Misc queries) | |||
Validation source field size and function. | Excel Worksheet Functions | |||
Data Validation Source Box | Excel Worksheet Functions |