Workbook before close problems

S

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
 
J

james.billy

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
 
C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Workbook reminder 0
Auto Close Yes no button 3
how to add a textbox if user chooses yes in response to msgbox. 0
Modified worksheet macro, where if Yes portion works but not else 4
another code not working 4
Checking a Status Control from a Form Before Proceeding2 1
In Before Close Sub ActiveWorkBook.Close(False) repeat procedure 5
cursor won't move to selection 2

Top