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

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

"Trevor Williams" wrote in
message ...
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

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.

--
Jim
"Trevor Williams" wrote in
message ...
| 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

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.

"Peter T" wrote:

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

"Trevor Williams" wrote in
message ...
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

Thanks Jim

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

Trevor

"Jim Rech" wrote:

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.

--
Jim
"Trevor Williams" wrote in
message ...
| 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

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

Regards,
Peter T


"Trevor Williams" wrote in
message ...
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.

"Peter T" wrote:

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

"Trevor Williams" wrote in
message ...
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