XL2002 - trigger sheet event BEFORE deactivating

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Trevor Williams - 25 Jan 2008 12:23 GMT

Hi All

I want to inform a user of incomplete cells if they attempt to move to a
different worksheet.

The Worksheet_Deactivate event takes the focus away from the current sheet
before running the code. Is there another event I can use that runs before
displaying the next sheet?

Thanks in advance

Trevor Williams

Reply to this Message

Peter T - 25 Jan 2008 12:43 GMT

Hi Trevor,

What you are looking for is a BeforeDeactivate event with a Cancel, and
there isn't one. Workaround is to reactivate the sheet if necessary

Private Sub Worksheet_Deactivate()
If Len(Range("A1")) = 0 Then ' empty cell A1
On Error GoTo errH:
' if necessary disable events
Application.EnableEvents = False
Me.Activate
Application.Goto Range("A1"), True
MsgBox "Complete cell A1"
End If
errH:
Application.EnableEvents = True
End Sub

Regards,
Peter T

> Hi All
>
[quoted text clipped - 8 lines]
>
> Trevor Williams

Reply to this Message

Trevor Williams - 25 Jan 2008 14:31 GMT

Hi Peter,

That's what I thought. Bit of an oversight on MS behalf. (perhaps it's in
2007)
I'm using navigation controls on my sheets so I'm going with Jim Rech's
option.

Thanks for the response.

> Hi Trevor,
>
[quoted text clipped - 30 lines]
> >
> > Trevor Williams

Reply to this Message

Peter T - 25 Jan 2008 14:43 GMT

You may need to trap Ctrl-PageUp/Down with too that approach

Regards,
Peter T

> Hi Peter,
>
[quoted text clipped - 39 lines]
> > >
> > > Trevor Williams

Reply to this Message

Jim Rech - 25 Jan 2008 12:45 GMT

I don't think so. We need a 'before deactivate' event with a cancel option.
As a result of this limitation I hide worksheet tabs and make users use my
buttons or menus instead.

Signature

Jim

| Hi All
|
[quoted text clipped - 8 lines]
|
| Trevor Williams

Reply to this Message

Trevor Williams - 25 Jan 2008 14:32 GMT

Thanks Jim

I'm using nav buttons so will bolt it onto them.

Trevor

> I don't think so. We need a 'before deactivate' event with a cancel option.
> As a result of this limitation I hide worksheet tabs and make users use my
[quoted text clipped - 13 lines]
> |
> | Trevor Williams

Reply to this Message