Reading Excel validation criteria

  Nigel-331402 14:14 30 Mar 2004

I have a macro that needs to scroll down a range of cells and carries out a certain action based upon the validation criteria for that cell. This is all well and good until a cell is reached that has no validation criteria.

At this point I get an error dialog displayed with '400' in it.

The line I am using is

temp = Range(target).Offset(offst, 0).Validation.Formula1

If I change the .formula1 to .Type I get the same error.

How can I determine if a cell has validation criteria or not ?


  VoG II 14:59 30 Mar 2004

A good question but I do not know the answer.

As a workaround you could use an error trap such as:

On Error Goto LabelA

code goes here


Onr Goto 0

or you could use

On Error Resume Next

which will go to the line following the one causing the error. This should be

On Error Goto 0

to turn error reporting back on.

  Nigel-331402 16:53 30 Mar 2004

I'd thought of that but I was hoping to find a more elegant solution.

Thanks anyway.

  Sir Radfordin 16:54 30 Mar 2004

If VoG doesn't know surely there can't be an answer????

  pc moron 17:19 30 Mar 2004

From Excel Help for VBA Trappable Errors.

Form already displayed; can't show modally (Error 400)

You can't use the Show method to display a visible form as modal. This error has the following cause and solution:

You tried to use Show, with the style argument set to 1 – vbModal, on an already visible form.

Use either the Unload statement or the Hide method on the form before trying to show it as a modal form.

  Nigel-331402 20:58 30 Mar 2004

pc moron

The thing is I'm not using forms and so I didn't try to use Show.

The following code can be modified to meet your needs


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error Resume Next

x = Target.Validation.Type

If Err <> 0 Then

MsgBox "No Validation"


MsgBox "There is some validation"

End If

End Sub

Courtesy MrE

  VoG II 23:18 30 Mar 2004

1) well at least I'm reassured that there isn't a straightforward direct way to do this.

2) I, too, am mystified as to how this works, Whisperer.

3) That code has to go in a worksheet code module. Right click the sheet and select View Code and paste it in. You probably know that, DumboFixer, but others reading this might not.

  VoG II 23:27 30 Mar 2004

On reflection, I do understand how it works. Still not straightforward and depends on detecting an error which is far from elegant (vide infra). Better than my crude approach though!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5T review: Hands-on

Illustrator Andrés Lozano on his improv line work, brazen use of colours & hand sketching

iPhone X review

Comment envoyer gratuitement des gros fichiers ?