Reading Excel validation criteria

  Nigel-331402 14:14 30 Mar 04
Locked

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 ?

Thanks

  VoG II 14:59 30 Mar 04

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

LabelA:

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 04

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

Thanks anyway.

  Sir Radfordin 16:54 30 Mar 04

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

  pc moron 17:19 30 Mar 04

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 04

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"

Else

MsgBox "There is some validation"

End If

End Sub

Courtesy MrE

  VoG II 23:18 30 Mar 04

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 04

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.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Alex Chinneck’s giant ice cube Christmas tree at Kings Cross

Apple rumours & predictions 2017: The iPhone 8, new iPads, and everything else you should expect fr7…