Try using "BeforeSave" rather than "BeforeClose"........
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)
Worksheets("test").Visible = xlSheetVeryHidden
end sub
On May 6, 12:05 pm, sa_bear <sh.humphr...@gmail.com> wrote:
> Hi,
>
> I need to hide a worksheet on closing the workbook.
>
> I have tried doing this using Workbook_BeforeClose and also
> Workbook_Open but these have not consistently perfomed this task.
>
> When using Workbook_BeforeClose and closing the workbook using the
> workbook x (not the excel x) the first time I test this, the sheet is
> hidden as expected. I choose Yes to save changes, the file closes.
> I reopen the file, the worksheet is hidden as expected, I expose the
> sheet and close the workbook using the workbook x again, saying Yes to
> save changes, file closes.
> I reopen the file and the worksheet is not hidden as expected.
>
> The code I am using is as follows (this is in the ThisWorksheet Object
> in the project)
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> Worksheets("test").Visible = xlSheetVeryHidden
> End Sub
>
> If I use the x to close Excel and the open file then the sheet gets
> hidden but this is done via my Workbook_Open code - as the msg box
> pops up as expected.
>
> Private Sub Workbook_Open()
> MsgBox "Workbook_Open() code triggered this"
> Worksheets("test").Visible = xlSheetVeryHidden
> End Sub
>
> If I open the file but without closing Excel, then the Workbook_Open
> code is not triggered and my sheet not hidden.
>
> So I haven't managed to find a consistent way to hide the sheet. I
> don't mind how I do this - on close or open - but just want it to be
> fool proof and never visible unless another macro (with password)
> enables it.
>
> I have obviously reviewed a large amount of other posts but none of
> these seem to point to the same inconsistencies as I'm seeing - I will
> try on my Excel at home to remove the possibility of a corrupt
> installation on my machine. But in the interim - does anyone have any
> experience with this issue?
>
> I'm running Excel 2003 SP2 (11.8109.8122)
>
> Thanks in advance for any of your time, advice or thoughts on this!
>
> Cheers, S
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
