Pop up macro

Pop up macro

Didn't find your answer?

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.

avatar
By ACDWebb
14th May 2013 14:55

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

Thanks (0)
avatar
By paulwakefield1
14th May 2013 17:39

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.

Thanks (0)
avatar
By ACDWebb
14th May 2013 18:25

Well

quite :)

Thanks (0)
avatar
By edhy
17th May 2013 09:24

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

 

Thanks (0)