The Workbook_BeforeClose is executed when the user attemps to exit Excel.
Your msgbox should appear before the Excel "Do you want to save changes..."
msgbox. If the user answers "No" to that one the changes you made (hidden
sheets) will not be saved. I think all you need to do is save the changes in
the Workbook_BeforeClose sub if the user answers "Yes" to your msgbox.

ThisWorkbook.Save

P.S. you should move the On Error and Sheet13.Visible lines outside the
For-Next loop

On Error Resume Next
For Each sht In ActiveWorkbook.Sheets
sht.Visible = 2
Next sht
Sheet13.Visible = xlSheetVisible


"st120869" wrote:


I am using the code listed below, works perfect if I choose the chose
the spreadsheet i.e hides sheets, remove custom menu, problem is when I
select no to closing the workbook closes anyway but does not remove
custom menu.

Can anmyone help - I need to code to not close workbook when no
selected.

Thanks


Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Msg1, Style, Title, Response, MyString
Msg1 = "Do you want Close the PRISM Model ?"
Style = vbYesNo + vbCritical + vbDefaultButton2
Title = "Close Model"
Response = MsgBox(Msg1, Style, Title)

If Response = vbYes Then
MyString = "Yes"
For Each sht In ActiveWorkbook.Sheets
sht.Visible = 2
On Error Resume Next
Sheet13.Visible = xlSheetVisible
Next sht
CallMenu.resetWorksheet_Menu_Bar
Application.Quit
Else
MyString = "no"

End If
End Sub


--
st120869
------------------------------------------------------------------------
st120869's Profile: http://www.excelforum.com/member.php...o&userid=18330
View this thread: http://www.excelforum.com/showthread...hreadid=493346