Before_Close event and hiding sheet

NobodysPerfect · #1 05-02-2014, 04:45 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
From an XL sheet ,how to keep the group of columns which match with other XL sheet	Zubairkhan	Excel	2	03-04-2014 10:57 PM
sheet 2 data highlight in sheet 1	gsrikanth	Excel	1	04-21-2012 06:25 PM
Construct a summary sheet by summing up from one or more than one sheet.	PRADEEPB270	Excel	1	11-04-2011 03:46 AM
Hiding text	Reg06	Visio	0	01-15-2011 08:04 AM
copy cell from sheet 2 to sheet 3 macro	slipperyjim	Excel Programming	1	02-18-2010 01:31 AM