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

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