Data validation with vba

Question

Normally it is possible to apply data validation in excel through vba but i am having a specific problem

I am using the indirect method for applying validation,normally when i dont use vba i get a warning by excel that "source currently evaluates to an error,do u want to continue?" (this is because cells which indirect refers to may be empty), now i can easily skip this error in excel by clicking "Yes"

Here's the link http://www.contextures.com/xldataval02.html (Dependent validation lists)

But when i try to perform the same thing using vba i am getting a runtime error,there is no friendly prompt which allows me to continue.How can i handle this kind of error.

On error resume isnt working because then vba doesnt apply validation at all.

Here's the code

Set rng = ThisWorkbook.Sheets("input").Range("AB11:AB65536")
With rng.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:= _
    "=INDIRECT(cablecode&""_depth_""&$Q11&""_""&$Z11&""_values"")"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

cablecode is a static named range

Q11 and Z11 refer to inputs in that particular row

depth and values are normal strings

cablecode can have two values = "is" or "iec"

Q11 can be "xlpe" or "pvc"

Z11 can be "al" or "cu"

since cablecode is constant for whole project ive referred to it directly, Q11 and Z11 can be different for different rows so ive referred to them seperately

The whole string comes out to be "is_depth_al_xlpe_values" similarly different permuations,and all named ranges are already defined

This will be more clear if you can kindly post the source code. — Edwin Bautista
– Edwin Bautista, Commented May 9, 2012 at 5:13
The error is occurring when vba tries to add validation..i am getting a runtime error '1004' "application defined or object defined error'..its due to the fact that validation will currently evaluate to an error and vba wont let me continues...whereas excel does.. — Kartik Anand
– Kartik Anand, Commented May 9, 2012 at 5:36

shahkalpesh · Accepted Answer · 2012-05-09 06:01:15Z

1

I suppose the following line needs correction from

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:= _
    "=INDIRECT(cablecode&""_depth_""&$Q11&""_""&$Z11&""_values"")"

to

dim cellToBeReferred as string
cellToBeReferred = cablecode & "_depth_" & Range("$Q11").Value & "_" _
& Range("$Q11").Value & "_values"

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:= _
    "=INDIRECT(" & cellToBeReferred  & ")"

Taking the string out will help you debug the issue and figure what is the value of named range, you'd want to refer to. See what value cellToBeReferred results into and correct the concatenation using above.

EDIT after reading OPs comment

In that case, surround the INDIRECT with ISERROR and give a dummy cell reference. For e.g.

"=IFERROR(INDIRECT(" & cellToBeReferred  & "), Z1)"

Where Z1 is a cell to be referred if INDIRECT fails.

edited May 9, 2012 at 6:01

answered May 9, 2012 at 5:48

shahkalpesh

33.6k4 gold badges66 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Kartik Anand Over a year ago

i know what the error is..its simply when Q11 and Z11 are not specified the string would become is_depth_(nothing here)_(nothing here)_values...since it is not a named range it gives an error..the problem is excel would let me continue because later i can input Q11 and Z11 and the validation will be available..but vba wont let me continue...

Kartik Anand Over a year ago

but in case of dummy cell reference the original validation wont be there..and if a user enters data in Z11 and Q11 he wont get the list he was supposed to

shahkalpesh Over a year ago

If the named range is proper & has data, it will be referred. In case of error, Z1 (which is a temporary reference) will be referred.

Kartik Anand Over a year ago

its still not working...its not catching that specific warning that excel shows in case indirect is currently pointing to error..(and yes i used "ISERROR")

Kartik Anand Over a year ago

Well i got it after getting your hint as "ISERROR",the thing is it still wont catch with the "ISERROR" statement because named ranges which consist of more than one cell return "#value" error if you use them on a cell.Instead i used "ISREF" Here's the better format "=IF(ISREF(INDIRECT(cablecode&""temp""&$Q11&""""&$Z11&""_values"")),INDIRECT (cablecode&""_temp""&$Q11&""_""&$Z11&""_values""),NA)"

J B · Accepted Answer · 2020-11-12 20:59:04Z

0

No way to suppress warning in VBA. Temporarily assign a value to the reference cell and clear it later. This will prevent the run-time error that's avoided by the UI when selecting "Yes" to "The source currently evaluates to an error. Do you want to continue" dialog box.

ex.

thisworkbook.range("Q11").value = "1"

'data validation - i.e. Formula1:="=INDIRECT(Q11)"

thisworkbook.range("Q11").value = ""

edited Nov 12, 2020 at 20:59

answered Nov 12, 2020 at 20:45

J B

11 bronze badge

Collectives™ on Stack Overflow

Data validation with vba

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related