STOP WORKBOOK CLOSING

[KandK]

Is there anyway of stopping someone closing a workbook unintentionally by
maybe having to enter a password or a warning coming up asking are you sure
you want to close. Anything to deter people from closing it. Can someone
please help.

[Tristan]

Hi KandK

What you're asking is possible. Don't know how versed you are in Excel programming so Janet adn Joh instuctions follow:

1. Open your file
2. Press Alt+F11. This will open the Visual Basic window
3. In the top left-hand window double click on "Worksheet"
4. Probably a large white window has just appeared (if it hasn't go to View - Code (F7)
5. Copy and paste the code below into the big white window.
6. Close the window to get back to the Excel worksheet.
7. Try and close it and hopefully a dialogue box will pop up asking if you're sure.

//////////////////////////////////////////////////////////////////////////////////
'Copy and Paste all the below
'
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Answer As VbMsgBoxResult

Cancel = True
Answer = MsgBox("Are you sure?", vbOKCancel, "Insert appropriate text here")
If Not Answer = vbCancel Then
Cancel = False
End If

End Sub

[KandK]

Thanks Tristan that worked excellently. I was wondering if it is possible to
actually password protect BeforeClose. I have looked at the Visual Basic help
but as I am new to using it I am afraid it is a bit over my head.
Once again thanks so much for the help.

"Tristan" wrote:

>
> Hi KandK
>
> What you're asking is possible. Don't know how versed you are in Excel
> programming so Janet adn Joh instuctions follow:
>
> 1. Open your file
> 2. Press Alt+F11. This will open the Visual Basic window
> 3. In the top left-hand window double click on "Worksheet"
> 4. Probably a large white window has just appeared (if it hasn't go to
> View - Code (F7)
> 5. Copy and paste the code below into the big white window.
> 6. Close the window to get back to the Excel worksheet.
> 7. Try and close it and hopefully a dialogue box will pop up asking if
> you're sure.
>
> //////////////////////////////////////////////////////////////////////////////////
> 'Copy and Paste all the below
> '
> Option Explicit
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
>
> Dim Answer As VbMsgBoxResult
>
> Cancel = True
> Answer = MsgBox("Are you sure?", vbOKCancel, "Insert appropriate
> text here")
> If Not Answer = vbCancel Then
> Cancel = False
> End If
>
> End Sub
>
>
> --
> Tristan
> ------------------------------------------------------------------------
> Tristan's Profile: http://www.excelforum.com/member.php...o&userid=34061
> View this thread: http://www.excelforum.com/showthread...hreadid=539033
>
>