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 |
Adding Validation When Source Validates To Error?
myWS.Application.DisplayAlerts = False
-- Regards, Tom Ogilvy "(PeteCresswell)" wrote: 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 |
Adding Validation When Source Validates To Error?
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 |
Adding Validation When Source Validates To Error?
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 "(PeteCresswell)" wrote: 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 |
Adding Validation When Source Validates To Error?
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... -- PeteCresswell |
All times are GMT +1. The time now is 10:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com