Message if Shared Workbook is deselected

[Pauljj]

I have a spreadsheet which is a Shared workbook. As this tracks the history of what happens to the spreasheet, If someone tries to deselect it I want a message to appear to with a YES and NO option button, so if they have not copied the history data the shared workbook remains, if they have copied the data and then click yes the shared workbook then becomes unticked and therfore exclusive...any ideas ?

 

[BrianB]

Something like this ? Copy/paste code to worksheet module (rightclick tab/View code)

Private Sub Worksheet_Deactivate()
   rsp = MsgBox("Deactivated. Save changes ?", vbYesNoCancel)
   If rsp = vbCancel Then Exit Sub
   If rsp = vbYes Then
        'do something
   Else
        ' do something else
   End If
End Sub

 

[Pauljj]

Ive inserted a module and copied this code across but nothing happens when I deselect the box ?

 

[Pauljj]

Any ideas how I can get this code to work please ??


Thanks

Paul

 

[BrianB]

The code does not do anything as it stands - except it runs when the someone moves away from the sheet it is contained in to another one. There is a similar possibility for the workbook. It is necessary to replace "'do something " and "'do something else" with appropriate code.

Apart from that I do not know codewise what you are trying to achieve. A way forward would be to record macros of the 2 possibilities and come back with any problems.