|
Workbook_BeforeClose
2nd attempt to post - 1st one lost in cyberspace I think
I have the following :- Private Sub Workbook_BeforeClose(Cancel As Boolean) ActiveWorkbook.Unprotect Application.ScreenUpdating = False Application.EnableEvents = False Sheets("Current Round").Unprotect Password:="x" Sheets("Current Round Detailed").Unprotect Password:="x" Sheets("Current Round Graph").Unprotect Password:="x" Sheets("Home Graphs").Unprotect Password:="x" Sheets("Home Detailed Graphs").Unprotect Password:="x" Sheets("All Rounds").Unprotect Password:="x" Sheets("View Rounds").Unprotect Password:="x" Sheets("Search Rounds").Unprotect Password:="x" Sheets("LogGraph3").Unprotect Password:="x" Sheets("Current Round").Visible = False Sheets("Current Round Detailed").Visible = False Sheets("Current Round Graph").Visible = False Sheets("Home Graphs").Visible = False Sheets("Home Detailed Graphs").Visible = False Sheets("All Rounds").Visible = False Sheets("LogGraph3").Visible = True Sheets("View Rounds").Visible = False Sheets("Search Rounds").Visible = False Sheets("RecordOfRounds").Visible = xlSheetVeryHidden Sheets("RecordOfRoundsDetailed").Visible = xlSheetVeryHidden Sheets("HomeCourse").Visible = xlSheetVeryHidden Sheets("HomeDetailed").Visible = xlSheetVeryHidden Sheets("LogGraph1").Visible = xlSheetVeryHidden Sheets("LogGraph2").Visible = xlSheetVeryHidden Sheets("LogGraph4").Visible = xlSheetVeryHidden Sheets("LogGraph5").Visible = xlSheetVeryHidden Sheets("LogGraph6").Visible = xlSheetVeryHidden Sheets("LogGraph7").Visible = xlSheetVeryHidden 'Sheets("Current Round").Select Dim wksht As Worksheet With Application .DisplayFormulaBar = True .ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)" End With For Each wksht In Worksheets(Array("Current Round", "Current Round Detailed", _ "Current Round Graph", "Home Detailed Graphs", "Home Graphs", "All Rounds", "View Rounds", "Search Rounds")) wksht.Activate ActiveWindow.DisplayHeadings = True Next Sheets("LogGraph3").Protect Password:="x" Application.ScreenUpdating = True Application.EnableEvents = True ActiveWorkbook.Protect Structu=True, Windows:=False End Sub Once this runs the "Do you want to save the changes....." dialog box pops up. If the "Yes" button is clicked - fine. If the "No" button is clicked - fine. If the "Cancel" button is clicked the visible window does not show the "Ribbon" nor is the top bar in the Excel application. In short there are no menus and no exit buttons to close the file or the application. Is there any way to force the "Ribbon" to display in order to access the Close buttons. Any help is appreciated. Sandy |
Workbook_BeforeClose
I think that you will need to trap the Save event, something like this
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim sFile Application.EnableEvents = False '<Optional - this would be before save code Cancel = True If SaveAsUI Then sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls") If sFile < False Then ThisWorkbook.SaveAs sFile '<Optional - this would be after save code End If Else ThisWorkbook.Save '<Optional - this would be after save code End If Application.EnableEvents = True End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sandy" wrote in message ... 2nd attempt to post - 1st one lost in cyberspace I think I have the following :- Private Sub Workbook_BeforeClose(Cancel As Boolean) ActiveWorkbook.Unprotect Application.ScreenUpdating = False Application.EnableEvents = False Sheets("Current Round").Unprotect Password:="x" Sheets("Current Round Detailed").Unprotect Password:="x" Sheets("Current Round Graph").Unprotect Password:="x" Sheets("Home Graphs").Unprotect Password:="x" Sheets("Home Detailed Graphs").Unprotect Password:="x" Sheets("All Rounds").Unprotect Password:="x" Sheets("View Rounds").Unprotect Password:="x" Sheets("Search Rounds").Unprotect Password:="x" Sheets("LogGraph3").Unprotect Password:="x" Sheets("Current Round").Visible = False Sheets("Current Round Detailed").Visible = False Sheets("Current Round Graph").Visible = False Sheets("Home Graphs").Visible = False Sheets("Home Detailed Graphs").Visible = False Sheets("All Rounds").Visible = False Sheets("LogGraph3").Visible = True Sheets("View Rounds").Visible = False Sheets("Search Rounds").Visible = False Sheets("RecordOfRounds").Visible = xlSheetVeryHidden Sheets("RecordOfRoundsDetailed").Visible = xlSheetVeryHidden Sheets("HomeCourse").Visible = xlSheetVeryHidden Sheets("HomeDetailed").Visible = xlSheetVeryHidden Sheets("LogGraph1").Visible = xlSheetVeryHidden Sheets("LogGraph2").Visible = xlSheetVeryHidden Sheets("LogGraph4").Visible = xlSheetVeryHidden Sheets("LogGraph5").Visible = xlSheetVeryHidden Sheets("LogGraph6").Visible = xlSheetVeryHidden Sheets("LogGraph7").Visible = xlSheetVeryHidden 'Sheets("Current Round").Select Dim wksht As Worksheet With Application .DisplayFormulaBar = True .ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)" End With For Each wksht In Worksheets(Array("Current Round", "Current Round Detailed", _ "Current Round Graph", "Home Detailed Graphs", "Home Graphs", "All Rounds", "View Rounds", "Search Rounds")) wksht.Activate ActiveWindow.DisplayHeadings = True Next Sheets("LogGraph3").Protect Password:="x" Application.ScreenUpdating = True Application.EnableEvents = True ActiveWorkbook.Protect Structu=True, Windows:=False End Sub Once this runs the "Do you want to save the changes....." dialog box pops up. If the "Yes" button is clicked - fine. If the "No" button is clicked - fine. If the "Cancel" button is clicked the visible window does not show the "Ribbon" nor is the top bar in the Excel application. In short there are no menus and no exit buttons to close the file or the application. Is there any way to force the "Ribbon" to display in order to access the Close buttons. Any help is appreciated. Sandy |
Workbook_BeforeClose
Problem is though Bob the 'BeforeSave' doesn't kick in if the cancel button
is clicked. And there are no close buttons in view to do anything further. Sandy "Bob Phillips" wrote in message ... I think that you will need to trap the Save event, something like this Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim sFile Application.EnableEvents = False '<Optional - this would be before save code Cancel = True If SaveAsUI Then sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls") If sFile < False Then ThisWorkbook.SaveAs sFile '<Optional - this would be after save code End If Else ThisWorkbook.Save '<Optional - this would be after save code End If Application.EnableEvents = True End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sandy" wrote in message ... 2nd attempt to post - 1st one lost in cyberspace I think I have the following :- Private Sub Workbook_BeforeClose(Cancel As Boolean) ActiveWorkbook.Unprotect Application.ScreenUpdating = False Application.EnableEvents = False Sheets("Current Round").Unprotect Password:="x" Sheets("Current Round Detailed").Unprotect Password:="x" Sheets("Current Round Graph").Unprotect Password:="x" Sheets("Home Graphs").Unprotect Password:="x" Sheets("Home Detailed Graphs").Unprotect Password:="x" Sheets("All Rounds").Unprotect Password:="x" Sheets("View Rounds").Unprotect Password:="x" Sheets("Search Rounds").Unprotect Password:="x" Sheets("LogGraph3").Unprotect Password:="x" Sheets("Current Round").Visible = False Sheets("Current Round Detailed").Visible = False Sheets("Current Round Graph").Visible = False Sheets("Home Graphs").Visible = False Sheets("Home Detailed Graphs").Visible = False Sheets("All Rounds").Visible = False Sheets("LogGraph3").Visible = True Sheets("View Rounds").Visible = False Sheets("Search Rounds").Visible = False Sheets("RecordOfRounds").Visible = xlSheetVeryHidden Sheets("RecordOfRoundsDetailed").Visible = xlSheetVeryHidden Sheets("HomeCourse").Visible = xlSheetVeryHidden Sheets("HomeDetailed").Visible = xlSheetVeryHidden Sheets("LogGraph1").Visible = xlSheetVeryHidden Sheets("LogGraph2").Visible = xlSheetVeryHidden Sheets("LogGraph4").Visible = xlSheetVeryHidden Sheets("LogGraph5").Visible = xlSheetVeryHidden Sheets("LogGraph6").Visible = xlSheetVeryHidden Sheets("LogGraph7").Visible = xlSheetVeryHidden 'Sheets("Current Round").Select Dim wksht As Worksheet With Application .DisplayFormulaBar = True .ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)" End With For Each wksht In Worksheets(Array("Current Round", "Current Round Detailed", _ "Current Round Graph", "Home Detailed Graphs", "Home Graphs", "All Rounds", "View Rounds", "Search Rounds")) wksht.Activate ActiveWindow.DisplayHeadings = True Next Sheets("LogGraph3").Protect Password:="x" Application.ScreenUpdating = True Application.EnableEvents = True ActiveWorkbook.Protect Structu=True, Windows:=False End Sub Once this runs the "Do you want to save the changes....." dialog box pops up. If the "Yes" button is clicked - fine. If the "No" button is clicked - fine. If the "Cancel" button is clicked the visible window does not show the "Ribbon" nor is the top bar in the Excel application. In short there are no menus and no exit buttons to close the file or the application. Is there any way to force the "Ribbon" to display in order to access the Close buttons. Any help is appreciated. Sandy |
| All times are GMT +1. The time now is 11:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com