Hi,
I'm using Excel 2007 and what I want is that when users click to close the file, a message pops up to remind them "Lock the worksheet before closing".
I feel sure that this isn't a particularly difficult macro to write, but Google has let me down on this ocassion.
Can anyone provide me with a couple lines of code and where I need to put it in my file to get this to happen?
Thanks all!
Sharon
Replies (4)
Please login or register to join the discussion.
Try this in ThisWorkbook
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Sheet1.ProtectContents = False Then 'change this to refer to the sheet to be protected
MsgBox prompt:="Lock the worksheet before closing", Buttons:=vbExclamation
Cancel = True
End If
End Sub
Or you could just have a macro
that protects the worksheet on closing.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheet1.protect
End Sub
You may want to expand this to add parameters and to handle any other sheets.
Protect all sheets in the workbook
To protect all sheets in the workbook use:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
For Each Sheet In Sheets
Sheet.Protect
Next
End Sub
Regards
Zubair Edhy