Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Workbook_BeforeClose
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 270
Default 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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default 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




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 270
Default 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




Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Workbook_BeforeClose Alan McQuaid via OfficeKB.com Excel Programming 3 June 13th 06 04:36 PM
Workbook_BeforeClose [email protected] Excel Programming 1 April 11th 06 10:53 AM
workbook_beforeClose GB Excel Programming 2 March 1st 06 12:39 AM
Workbook_BeforeClose PO Excel Programming 2 January 30th 05 05:07 PM
Workbook_BeforeClose Don Guillett[_4_] Excel Programming 1 August 31st 04 02:27 PM


All times are GMT +1. The time now is 06:58 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement