Data Validation error

antonhoward

New Member
Joined
Jan 24, 2008
Messages
9
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:confused:
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

sous2817

Well-known Member
Joined
Feb 22, 2008
Messages
2,276
Does putting

Code:
Application.DisplayAlerts = False
at the start of your code and

Code:
Application.DisplayAlerts = True
at the end work?
 

sous2817

Well-known Member
Joined
Feb 22, 2008
Messages
2,276
What line is giving you that error?
 

antonhoward

New Member
Joined
Jan 24, 2008
Messages
9
This is the line in question.

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

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Jan 24, 2008
Messages
9
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Jan 24, 2008
Messages
9
Woohoo!!

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

Watch MrExcel Video

Forum statistics

Threads
1,099,164
Messages
5,467,003
Members
406,516
Latest member
richcresswell

This Week's Hot Topics

Top