Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA to set Excel Data Validation
Hey there,
I currently use a 'two-tier' data validation rule. the first being: Data¦Validation¦List Source: =List1 'this has 4 arguments The second list is fed from this List1 as: =CHOOSE(VLOOKUP(E4,List1_Elements,2,FALSE),List2a, List2b,List2c,List2d) When applying the second validation, Excel naturally gives an eror: 'The source currently equates to an error. Do you wish to continue?' I press 'Yes' and the process works.... I need to recreate this setup process in VBA. However, when I get to the .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:= _ "=CHOOSE(VLOOKUP(E4,List1_Elements,2,FALSE),List2a ,List2b,List2c,List2d)" VBA crashes as it cannot handle the Excel error.... How do i get around this?!? thx |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA to set Excel Data Validation
Hi
Try Dim response as Variant err.clear On Error Resume Next .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:= _ "=CHOOSE(VLOOKUP(E4,List1_Elements,2,FALSE),List2a ,List2b,List2c,List2d)" If err.number<0 then Response = Msgbox("The source currently equates to an error." & vbcr _ & "Do you wish to continue?", vbYesNo, "Error Message") If Response = vbYes then 'some code Else 'some other code end if end if On error Goto 0 regards Paul KurviousKoala wrote: Hey there, I currently use a 'two-tier' data validation rule. the first being: Data¦Validation¦List Source: =List1 'this has 4 arguments The second list is fed from this List1 as: =CHOOSE(VLOOKUP(E4,List1_Elements,2,FALSE),List2a, List2b,List2c,List2d) When applying the second validation, Excel naturally gives an eror: 'The source currently equates to an error. Do you wish to continue?' I press 'Yes' and the process works.... I need to recreate this setup process in VBA. However, when I get to the .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:= _ "=CHOOSE(VLOOKUP(E4,List1_Elements,2,FALSE),List2a ,List2b,List2c,List2d)" VBA crashes as it cannot handle the Excel error.... How do i get around this?!? thx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
custom data validation on cells with data validation values | Excel Worksheet Functions | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
Data validation causing problems when using a data form in Excel 2 | Excel Worksheet Functions | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) |