Validation and events

[Chrissirhc]

Hi,

Can I have a validation object (Data --> Validation)

If it is selected, is there an event to run some code?

Thanks,

Chris

 

[AnotherHiggins]

Here is a nice list of events:

http://www.cpearson.com/excel/events.htm

Why not use something like Worksheet_Change and check for whatever data in the validated cell/column/row/range?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Hi,

There is not event to selecting a validation object.

However, there is a Selection_Change event...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim t
    On Error Resume Next
    t = Target.Validation.Type
    If Err.Number <> 0 Then
        MsgBox "NO VAL"
    Else
        MsgBox "VAL"
    End If
End Sub

Skip,

_[red][/red]

 

[Chrissirhc]

Unfortunately I've tried:

Sub worksheet_selectionchange(ByVal target As Range)

Debug.Print target.Name

End Sub

And this fails even though that cell has a range name. The VB error I get is runtime 1004. "aplpication-defined or object-defined error"

I could use the address as a check, but someone may add a row or column onto the sheet which would mess it up. Looks like there isn't an event specifically for the validation object.

Any ideas?

Cheers,

Chris

 

[SkipVought]

You should be running this code in the SHEET OBJECT and not in a module?

Works for me.

Skip,

_[red][/red]

 

[Chrissirhc]

Yes I am running it in the sheet object... The range doesn't seem to have a name in the code...

 

[SkipVought]

What method di you use to name the range?

Can you see the Range name in the Name Box?

BTW, to actually get the NAME...

Sub worksheet_selectionchange(ByVal target As Range)
On Error Resume Next
MsgBox target.Name[b].Name[/b]

End Sub

Skip,

_[red][/red]

 

[Chrissirhc]

I get errors on when using target.name so not sure why it would be ok when I do name.name...?

I'll give it a quick try though and let you know...

 

[Chrissirhc]

Sub worksheet_selectionchange(ByVal target As Range)
On Error GoTo errorHandler
Debug.Print target.Name.Name

errorHandler:
Err.Clear

End Sub


Well I could use this I guess... However, I've already given the sheet to the user so he has to hit a button to do the stuff instead....

Cheers,

Chris

 

[Chrissirhc]

How exactly does .name.name work?

Thanks,

Chris

 

[SkipVought]

How to use the Watch Window as a Power Programming Tool faq707-4594

Skip,

_[red][/red]