Thread: Before_Close event and hiding sheet
View Single Post
 
Old 05-02-2014, 04:45 AM
NobodysPerfect NobodysPerfect is offline Windows 7 64bit Office 2010 32bit
Competent Performer
  
Join Date: Jan 2014
Location: Germany
Posts: 136
NobodysPerfect is on a distinguished road
Default Before_Close event and hiding sheet
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.

Code:
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
Reply With Quote