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
I have the following but it does not run when closing workbook - Any ideas?
(I am assuming the message box should pop up?)

Private Sub Workbook_BeforeClose(Cancel As Boolean)

MsgBox "Hello"

Application.ScreenUpdating = False
Application.EnableEvents = False

Sheets("Current Round").Unprotect Password:="xxxxx"
Sheets("Current Round Detailed").Unprotect Password:="xxxxx"
Sheets("Current Round Graph").Unprotect Password:="xxxxx"
Sheets("Home Graphs").Unprotect Password:="xxxxx"
Sheets("Home Detailed Graphs").Unprotect Password:="xxxxx"
Sheets("All Rounds").Unprotect Password:="xxxxx"
Sheets("View Rounds").Unprotect Password:="xxxxx"


Sheets("Current Round").Visible = True
Sheets("Current Round Detailed").Visible = True
Sheets("Current Round Graph").Visible = True
Sheets("Home Graphs").Visible = True
Sheets("Home Detailed Graphs").Visible = True
Sheets("All Rounds").Visible = True
Sheets("View Rounds").Visible = True

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("LogGraph3").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
.CommandBars("Worksheet Menu Bar").Enabled = True
.CommandBars("Standard").Visible = True
.CommandBars("Formatting").Visible = True
.CommandBars("Drawing").Visible = 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", "LogGraph3"))

Wksht.Activate
ActiveWindow.DisplayHeadings = True

Next

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Thanks
Sandy


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,501
Default Workbook_BeforeClose
Hi,

If this isn't running and I agree it should pop up the message box then
events may be disabled.
rRght click any sheet tab, view code and paste this in and run it

Sub enableem()
Application.EnableEvents = True
End Sub

then try you before_close routine again. When disbling events using code you
should do so at the last point in the code possible. If you disable early and
the code crashes you can be left with events disabled.

Mike

"Sandy" wrote:

I have the following but it does not run when closing workbook - Any ideas?
(I am assuming the message box should pop up?)

Private Sub Workbook_BeforeClose(Cancel As Boolean)

MsgBox "Hello"

Application.ScreenUpdating = False
Application.EnableEvents = False

Sheets("Current Round").Unprotect Password:="xxxxx"
Sheets("Current Round Detailed").Unprotect Password:="xxxxx"
Sheets("Current Round Graph").Unprotect Password:="xxxxx"
Sheets("Home Graphs").Unprotect Password:="xxxxx"
Sheets("Home Detailed Graphs").Unprotect Password:="xxxxx"
Sheets("All Rounds").Unprotect Password:="xxxxx"
Sheets("View Rounds").Unprotect Password:="xxxxx"


Sheets("Current Round").Visible = True
Sheets("Current Round Detailed").Visible = True
Sheets("Current Round Graph").Visible = True
Sheets("Home Graphs").Visible = True
Sheets("Home Detailed Graphs").Visible = True
Sheets("All Rounds").Visible = True
Sheets("View Rounds").Visible = True

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("LogGraph3").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
.CommandBars("Worksheet Menu Bar").Enabled = True
.CommandBars("Standard").Visible = True
.CommandBars("Formatting").Visible = True
.CommandBars("Drawing").Visible = 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", "LogGraph3"))

Wksht.Activate
ActiveWindow.DisplayHeadings = True

Next

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Thanks
Sandy


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 270
Default Workbook_BeforeClose
Hi Mike

Yes my stupidity, events had been turned off in the Workbook_Open.

Thanks
Sandy

"Mike H" wrote in message
...
Hi,

If this isn't running and I agree it should pop up the message box then
events may be disabled.
rRght click any sheet tab, view code and paste this in and run it

Sub enableem()
Application.EnableEvents = True
End Sub

then try you before_close routine again. When disbling events using code
you
should do so at the last point in the code possible. If you disable early
and
the code crashes you can be left with events disabled.

Mike

"Sandy" wrote:

I have the following but it does not run when closing workbook - Any
ideas?
(I am assuming the message box should pop up?)

Private Sub Workbook_BeforeClose(Cancel As Boolean)

MsgBox "Hello"

Application.ScreenUpdating = False
Application.EnableEvents = False

Sheets("Current Round").Unprotect Password:="xxxxx"
Sheets("Current Round Detailed").Unprotect Password:="xxxxx"
Sheets("Current Round Graph").Unprotect Password:="xxxxx"
Sheets("Home Graphs").Unprotect Password:="xxxxx"
Sheets("Home Detailed Graphs").Unprotect Password:="xxxxx"
Sheets("All Rounds").Unprotect Password:="xxxxx"
Sheets("View Rounds").Unprotect Password:="xxxxx"


Sheets("Current Round").Visible = True
Sheets("Current Round Detailed").Visible = True
Sheets("Current Round Graph").Visible = True
Sheets("Home Graphs").Visible = True
Sheets("Home Detailed Graphs").Visible = True
Sheets("All Rounds").Visible = True
Sheets("View Rounds").Visible = True

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("LogGraph3").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
.CommandBars("Worksheet Menu Bar").Enabled = True
.CommandBars("Standard").Visible = True
.CommandBars("Formatting").Visible = True
.CommandBars("Drawing").Visible = 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", "LogGraph3"))

Wksht.Activate
ActiveWindow.DisplayHeadings = True

Next

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Thanks
Sandy


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 477
Default Workbook_BeforeClose
"had been turned off in the Workbook_Open"

Sandy, I believe it is better to say that when events are turned off ALL
EVENTS are turned off, not just the Workbook_Open. << FWIW

Jim

"Sandy" wrote:

Hi Mike

Yes my stupidity, events had been turned off in the Workbook_Open.

Thanks
Sandy

"Mike H" wrote in message
...
Hi,

If this isn't running and I agree it should pop up the message box then
events may be disabled.
rRght click any sheet tab, view code and paste this in and run it

Sub enableem()
Application.EnableEvents = True
End Sub

then try you before_close routine again. When disbling events using code
you
should do so at the last point in the code possible. If you disable early
and
the code crashes you can be left with events disabled.

Mike

"Sandy" wrote:

I have the following but it does not run when closing workbook - Any
ideas?
(I am assuming the message box should pop up?)

Private Sub Workbook_BeforeClose(Cancel As Boolean)

MsgBox "Hello"

Application.ScreenUpdating = False
Application.EnableEvents = False

Sheets("Current Round").Unprotect Password:="xxxxx"
Sheets("Current Round Detailed").Unprotect Password:="xxxxx"
Sheets("Current Round Graph").Unprotect Password:="xxxxx"
Sheets("Home Graphs").Unprotect Password:="xxxxx"
Sheets("Home Detailed Graphs").Unprotect Password:="xxxxx"
Sheets("All Rounds").Unprotect Password:="xxxxx"
Sheets("View Rounds").Unprotect Password:="xxxxx"


Sheets("Current Round").Visible = True
Sheets("Current Round Detailed").Visible = True
Sheets("Current Round Graph").Visible = True
Sheets("Home Graphs").Visible = True
Sheets("Home Detailed Graphs").Visible = True
Sheets("All Rounds").Visible = True
Sheets("View Rounds").Visible = True

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("LogGraph3").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
.CommandBars("Worksheet Menu Bar").Enabled = True
.CommandBars("Standard").Visible = True
.CommandBars("Formatting").Visible = True
.CommandBars("Drawing").Visible = 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", "LogGraph3"))

Wksht.Activate
ActiveWindow.DisplayHeadings = True

Next

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Thanks
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 02:01 AM.

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