Hiding Worksheet on save event

[DavidB]

Hi there

I have written the following bit of code triggered off the workbook_before save event.


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' Hide the UPS worksheet on the Workbook_BeforeSave event
If Sheets("UPS").Visible <> xlVeryHidden Then
Debug.Print "Sheets(""UPS"").Visible = "; Sheets("UPS").Visible
Sheets("UPS").Visible = xlVeryHidden
' For an unknown reason the previous line does not execute when the before_save event is triggered by a program.
'Debug.Print "Sheets(""UPS"").Visible = "; Sheets("UPS").Visible
End If
End Sub
Sub SaveIt()
ActiveWorkbook.Save
End Sub


The code is in the Thisworkbook object and works perfectly from the file save menu path or the save icon.

My problem is when another macro saves the workbook the
Sheets("UPS").Visible = xlVeryHidden
line does not execute.

Is this by design or have I found a bug? I am using Excel 2003.

Regards David

 

[Andrew Poulsom]

Is it because ActiveWorkbook is not ThisWorkbook when you run your SaveIt procedure?

 

[DavidB]

Hi Andrew

This code is an extract from a much larger application, I have distilled it down to the problem code only. This distillation is contained in a stand-alone workbook and only that workbook is open so there should be no confusion about which workbook to operate on.

I have stepped through the code and the highlighting visits that line but does not appear to execute it. The debug.print lines before and after when uncommented perform as expected.

This morning I have tried two more minor changes with the same result.

1) Moved the SaveIt code from the module to the workbook object.

2) Changed the problem line to
Sheets("UPS").Visible = xlVeryHidden: Debug.Print "Hello"
Both sub portions of this line are visited during the line by line execution and the debug.Print returns the expected "Hello"

I am a little confused.

Regards David

 

[Andrew Poulsom]

I can reproduce your problem in Excel 2003. Sorry, I don't have an explanation or a solution.

 

[DavidB]

Thanks for your help Andrew

Odd behaviour to say the least.

I have come up with an interim solution. I just have to ensure that all prior to using the line "ActiveWorkbook.Save " I must ensure that the WorkSheet is very hidden.

I do not beleive that the code should behave like this and I don't like having to remember such work arounds. I am fortunate that the calls will only occur in a very limited number of places.

Regards DavidB

 

[Oele]

Hi DavidB,

This is a know issue / problem. Silly but it is.
http://support.microsoft.com/kb/898511/en-us

I allways use the following method:
In ThisWorkbook:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Run "Clear_The_Stuff"
End Sub

In a Module called "Clearing"

Option Explicit

Sub Clear_The_Stuff()
    '... other code ...
    If Sheets("UPS").Visible = True Then
        Sheets("UPS").Visible = xlVeryHidden
    End If
End Sub

And in a Code Modules:

Option Explicit

Sub YourCode()
    '... other code ...
    Run "Clear_The_Stuff"
    ThisWorkbook.Save
End Sub

In this way you have covered both ways of Saving the file.

PS:
I prefer to use special Modules for special Code so I can find them easely.
Naming Modules (referring to what they are for) and so grouping Macro's is much easier then Module1 till Module 7.

Succes. :wink:

 

[DavidB]

Thanks Oele

I don't feel so bad now knowing that I am not alone.

Regards DavidB

 

[Oele]

My pleasure .