Data Validation error

[antonhoward]

Hello,
Can someone please respond as I'm going nuts here.

I have a macro written that creates data validation in a spreadsheet. The Validation boxes are dependant on the previous box being filled in and I've used the indirect function for this.

The first validation boxes create fine being populated with a named range, however in the dependant validation boxes that follow I get the error "The source currently evaluates to an error, do you wish to continue" because a selection from the first box has not yet been made.
I need to stop this error from appearing or bypass it in some way using the macro but I'm lost.

Range("C81:D81").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=division"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("E81:F81").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=indirect($C$81)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = False
End With
Range("G81:H81").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=indirect($E$81)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = False
End With

Please help

 

[sous2817]

Does putting

Application.DisplayAlerts = False

at the start of your code and

Application.DisplayAlerts = True

at the end work?

 

[Jonmo1]

Try

Application.DisplayAlerts = False

 

[antonhoward]

Try

Application.DisplayAlerts = False

Tried that, I now get the error 1004 application defined or object defined error.
Any more ideas?

 

[sous2817]

What line is giving you that error?

 

[antonhoward]

This is the line in question.

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=indirect($C$81)"

 

[Jonmo1]

It looks like a valid option is going to have to be chosen from the previous validation cell (C81).. Before it can be used indirectly for another validation cell..

 

[antonhoward]

I was hoping that would not be the answer. I intend the form to be blank when it's finished so from what you are saying it looks like I will need to populate the first box until all validations are created and then empty the validations afterwards.

I'll give it a go but keep me posted if any other ideas come about.

 

[Jonmo1]

How about

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=if($C$81="""",Z1000,indirect($C$81))"

Where Z1000 is just some blank cell anywhere on your sheet...

 

[antonhoward]

Woohoo!!

That's done the trick, I knew there must be an easy solution.
Thanks so much jonmo1