Before_Close event and hiding sheet

[NobodysPerfect]

Hi to all!

In a global AddIn I have (among others) a single-line macro "ActiveWorkbook.Close SaveChanges:=True" with a respective button in the QAT.

Depending on the workbook to be closed and saved, the Workbook_BeforeClose event does some clean up. For one workbook I'd like to hide (xlVeryHidden) one special sheet.

If ActiveWorkbook.Sheets(MyName).Visible = True Then
    MsgBox ActiveWorkbook.Sheets(MyName) & " still visible"
    ActiveWorkbook.Sheets(MyName).Visible = xlVeryHidden
End If

All other code within the Workbook_BeforeClose procedure does fine, the "ActiveWorkbook.Sheets(MyName).Visible = xlVeryHidden" line does not. I even get the MsgBox informing that the sheet is still visible, but then it will not be hidden. Whenever I close the workbook using the closing "x", the sheet in fact is hidden and I am asked if I want to save the changes.

So what I do not understand: if Workbook_BeforeClose is triggered using the SaveWithChanges button, all desired changes are made before closing the file EXCEPT hiding the sheet. Even if I stop the code and proceed with F8, the code only seems to be executed. No error, just nothing.

Maybe I could–somehow–understand what happens if no changes at all were made. But all are, just hiding is not.




Any idea(s)? Help would be really appreciated.

Thanks
NP

[macropod]

Have you tried closing & re-opening the workbook after executing your macro to see whether the sheet has, in fact, been hidden? If it is, the issue is probably one of screen updates not occurring while your code executes.

[NobodysPerfect]

Thanks for your answer.

Yes, i did - several times and always the same result: if Workbook_BeforeClose is triggered using the SaveWithChanges button, the sheet isn't hidden, while it is whenever I select "Close" or closing "X".

What really strikes my: all other code lines are executed, the macro runs through the 'hide' code line, too, but the sheet isn't hidden.

I found the same problem here: http://www.ozgrid.com/forum/showthread.php?t=150539 ... unfortunately without a real solution.

NP

[macropod]

Aside from any issues to do with making the Workbook_BeforeClose macro work correctly, what would probably be a better approach is to intercept the workbook's Save event, via a Workbook_BeforeSave macro. That way, you can:
(a) capture the current visibility state for your worksheet;
(b) hide the worksheet;
(c) save;
(d) restore the pre-save visibility; and, finally
(e) set .Saved = True
That way, you don't need a Workbook_BeforeClose macro, since what's saved will always have the hidden setting (i.e. users will be unable to save the workbook with the sheet visible, even though they may be able to see it).

[NobodysPerfect]

Hi Paul,

maybe that's a good workaround: as soon as my new notebook is set up, I'll try it.

Thanks
NP

[NobodysPerfect]

Hi Paul,

PROBLEM SOLVED!

Thanks for the 'Before_Save' workaround
(... although, I'd really like to understand why the Before_Close doesnt work ...)

Cheers
NP