D
Dave Peterson
Since you liked Harlan's suggestion better than mine (I did too), I don't want
to look at mine anymore <bg>.
But the easiest question first: Which fires first--_beforeclose or _beforesave?
Put these in a brand new test workbook:
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "hi from beforeclose"
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox "hi from beforesave"
End Sub
And make a change and close the workbook. You'll see the order right away.
In fact, Chip Pearson has nice instructions for working with events at:
http://www.cpearson.com/excel/events.htm
He includes a workbook that you can download that has this kind of stuff in it.
So you can see the order of lots of events firing.
It's not hiding the sheets when you open it, it's hiding the sheets each time
you save it.
When you save it,
your code hides the sheets
saves the workbook
unhides the worksheets (which means the workbook is marked as needing a save
But then your code lies to excel telling it the workbook is saved, so if
you close without saving again, it has the sheets hidden.
That'll be difficult to do!
I made some minor changes to your code. I got rid of the Public declaration and
added two Dim's within a couple of procedures--you weren't really using it for
public purposes and it seems safer to keep in encased(?) in each procedure.
I removed some of your (obvious <bg>) comments. I find it difficult to read the
code when there's too many of that type comment.
"ws.visible = true" shouldn't need a comment (I bet you agree, too.)
And I added a return to range (that's the curSelection variable and
application.goto). I added the .screenupdating = false to help stop the
flicker--my pc had a slight case of the jitters, though--but hardly noticeable.
And I've stopped using "As Integer". Longs achieve the same purpose and it's
one less thing for me to remember <g>.
And I removed a few "on error resume next" lines. I didn't see any reason for
them to exist.
So you could try this version--remember the user can do a few things to still
break this--disable events and even though you stopped File|SaveAs within your
code, you can still copy it to a new location in windows explorer.
Anyway, here's what I did. I couldn't break you're code--but you'll want to
test the heck out of it:
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim intResp As Long
If Me.Saved = True Then Exit Sub
intResp = MsgBox("Do you want to save the changes you made to - '" _
& ActiveWorkbook.Name & "'?", vbYesNoCancel + vbExclamation, _
"Microsoft Excel")
Select Case intResp
Case vbYes
Call Workbook_BeforeSave(False, False) 'hide, save, exit
Case vbNo
Me.Saved = True 'exit
Case vbCancel
Cancel = True 'stay
End Select
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim WS As Worksheet
Dim CurSelection As Range
Set CurSelection = Selection
Cancel = True
'prevent file from being saved elsewhere
If SaveAsUI = True Then
MsgBox "Restriction on FileSaveAs location!", vbCritical
Cancel = True
Exit Sub
End If
'hide the flickering
Application.ScreenUpdating = False
'hide every sheet before save,
'whether workbook is being closed or not
For Each WS In Me.Worksheets
If LCase(WS.CodeName) = "sheet2" Then
'do not hide the titlesheet
Else
WS.Visible = xlSheetVeryHidden
End If
Next WS
Application.EnableEvents = False
ActiveWorkbook.Save
Application.EnableEvents = True
'if workbook is not being closed,
'unhide every Sheet
For Each WS In Me.Worksheets
WS.Visible = xlSheetVisible
Next WS
With Application
'go back to original location
.Goto CurSelection
'and reset screenupdating
.ScreenUpdating = True
End With
'else save message comes up twice,
'once for beforeclose,
'once for beforesave
Me.Saved = True
End Sub
Private Sub Workbook_Open()
Dim WS As Worksheet
For Each WS In Me.Worksheets
WS.Visible = xlSheetVisible
Next WS
End Sub
to look at mine anymore <bg>.
But the easiest question first: Which fires first--_beforeclose or _beforesave?
Put these in a brand new test workbook:
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "hi from beforeclose"
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox "hi from beforesave"
End Sub
And make a change and close the workbook. You'll see the order right away.
In fact, Chip Pearson has nice instructions for working with events at:
http://www.cpearson.com/excel/events.htm
He includes a workbook that you can download that has this kind of stuff in it.
So you can see the order of lots of events firing.
icestationzbra < said:hi dave/john/harlan,
i tried all the codes, spent nearly 36 hours performing regressive
test.
dave - the code that i got from you, there is a small glitch.
harlan - the code works fine, but i could not understand certain
things. what happens when i close the file, say no to save changes and
open the file with macros disabled? how is it hiding the sheets?
beforesave event is not being triggered, there is no sheet-hide
procedure in beforeclose.
It's not hiding the sheets when you open it, it's hiding the sheets each time
you save it.
When you save it,
your code hides the sheets
saves the workbook
unhides the worksheets (which means the workbook is marked as needing a save
But then your code lies to excel telling it the workbook is saved, so if
you close without saving again, it has the sheets hidden.
most of the final code is built around harlan's algorithm. i am still
trying to find a glitch or two in his code .
That'll be difficult to do!
I made some minor changes to your code. I got rid of the Public declaration and
added two Dim's within a couple of procedures--you weren't really using it for
public purposes and it seems safer to keep in encased(?) in each procedure.
I removed some of your (obvious <bg>) comments. I find it difficult to read the
code when there's too many of that type comment.
"ws.visible = true" shouldn't need a comment (I bet you agree, too.)
And I added a return to range (that's the curSelection variable and
application.goto). I added the .screenupdating = false to help stop the
flicker--my pc had a slight case of the jitters, though--but hardly noticeable.
And I've stopped using "As Integer". Longs achieve the same purpose and it's
one less thing for me to remember <g>.
And I removed a few "on error resume next" lines. I didn't see any reason for
them to exist.
So you could try this version--remember the user can do a few things to still
break this--disable events and even though you stopped File|SaveAs within your
code, you can still copy it to a new location in windows explorer.
Anyway, here's what I did. I couldn't break you're code--but you'll want to
test the heck out of it:
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim intResp As Long
If Me.Saved = True Then Exit Sub
intResp = MsgBox("Do you want to save the changes you made to - '" _
& ActiveWorkbook.Name & "'?", vbYesNoCancel + vbExclamation, _
"Microsoft Excel")
Select Case intResp
Case vbYes
Call Workbook_BeforeSave(False, False) 'hide, save, exit
Case vbNo
Me.Saved = True 'exit
Case vbCancel
Cancel = True 'stay
End Select
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim WS As Worksheet
Dim CurSelection As Range
Set CurSelection = Selection
Cancel = True
'prevent file from being saved elsewhere
If SaveAsUI = True Then
MsgBox "Restriction on FileSaveAs location!", vbCritical
Cancel = True
Exit Sub
End If
'hide the flickering
Application.ScreenUpdating = False
'hide every sheet before save,
'whether workbook is being closed or not
For Each WS In Me.Worksheets
If LCase(WS.CodeName) = "sheet2" Then
'do not hide the titlesheet
Else
WS.Visible = xlSheetVeryHidden
End If
Next WS
Application.EnableEvents = False
ActiveWorkbook.Save
Application.EnableEvents = True
'if workbook is not being closed,
'unhide every Sheet
For Each WS In Me.Worksheets
WS.Visible = xlSheetVisible
Next WS
With Application
'go back to original location
.Goto CurSelection
'and reset screenupdating
.ScreenUpdating = True
End With
'else save message comes up twice,
'once for beforeclose,
'once for beforesave
Me.Saved = True
End Sub
Private Sub Workbook_Open()
Dim WS As Worksheet
For Each WS In Me.Worksheets
WS.Visible = xlSheetVisible
Next WS
End Sub