Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook before close problems
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook before close problems
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook before close problems
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook before close problems
James, THanks a perfect solution -- st120869 ------------------------------------------------------------------------ st120869's Profile: http://www.excelforum.com/member.php...o&userid=18330 View this thread: http://www.excelforum.com/showthread...hreadid=493346 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook before close problems
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help on Workbook close and workbook save events | Excel Programming | |||
Before workbook close | Excel Programming | |||
close only one workbook | New Users to Excel | |||
So close! Problems with Loop | Excel Programming | |||
Close a the current workbook and load another specified workbook | Excel Programming |