#1
|
|||
|
|||
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 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 |
#2
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
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 |
#4
|
||||
|
||||
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).
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Hi Paul,
maybe that's a good workaround: as soon as my new notebook is set up, I'll try it. Thanks NP |
#6
|
|||
|
|||
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 |
|
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 |