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:
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Does putting

Code:
Application.DisplayAlerts = False

at the start of your code and

Code:
Application.DisplayAlerts = True

at the end work?
 
Upvote 0
This is the line in question.

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=indirect($C$81)"
 
Upvote 0
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..
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top