Workbook before close problems

[st120869]

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

[[email protected]]

Hi,

As you have put your code in the before workbook close event you need
to tell excel to stop the close if the user selects no, (currently you
are just letting the code finish which will then close the workbook).

Rewrite the Else statement as:

Else
Mystring = "no"
Cancel = True ' Cancel the workbook close event
Endif
End Sub

Any problems then post back

Regards,

James

[st120869]

James,
THanks a perfect solution

[Norman Jones]

Hi ST120869,

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

Try changing:

> Else
> MyString = "no"

to

Else
Cancel = True
MyString = "no"

---
Regards,
Norman


"st120869" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Charlie]

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
>
>