Workbook_BeforeClose

[Sandy]

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 Structure:=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

 

[Bob Phillips]

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]

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