Worksheet Deactivate (process before closing)

[cedricthecat]

Hi folks

Is there a way to use Worksheet_Deactivate to navigate to a particular location in a Worksheet before moving to another Worksheet

After looking at this thread I have realised that a Workbook I have been using with sheet protection allows users to look at "locked" sheets by clicking and holding on the name tab (they can't access the sheets to edit them, but they can view contents - nothing contentious, but I rather they couldn't do even this)

As a work-around I thought I could move to the top of the Worksheet on deactivate (only headers there) and then move back to the current date on activate. The latter works, but the former won't as the action is not being executed on the sheet I'm going to rather than the one I am coming from

I hope this makes sense!

Any help would be appreciated!

 

[VoG]

Hi Dave. this seems to work

Private Sub Worksheet_Deactivate()
Dim ws As Worksheet
Set ws = ActiveSheet
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Goto Me.Range("A1"), True
ws.Select
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[jim may]

Maybe something like this?

Private Sub Worksheet_Deactivate()
Ans = MsgBox("Do you wish to run code on the just deactivated sheet, before exiting?", vbQuestion + vbYesNoCancel)
If Ans = vbNo Or Ans = vbCancel Then
' Do nothing allowing user to jump to new sheet
Else
Me.Activate
'enter code here to perform task on the deactivated sheet like (example)...
ActiveSheet.Range("A1").Select


End If
End Sub

 

[cedricthecat]

Hi Peter

Thanks for this!

Sadly it doesn't like:-

Application.Goto Me.Range("A1"), True

giving me

run-time error '1004'

Method 'Goto' of object '_Application' failed

The sheet is protected but I added an unprotect to the start, so not sure what is going on??

 

[VoG]

Try

Private Sub Worksheet_Deactivate()
Dim ws As Worksheet
Set ws = ActiveSheet
Application.ScreenUpdating = False
Application.EnableEvents = False
Me.Unprotect
Application.Goto Me.Range("A1"), True
Me.Protect
ws.Select
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[cedricthecat]

Both of those solutions work!

Many thanks Peter and Jim!