Hiding sheets

[LAS]

I hope someone has an idea. I must be missing something obvious.

When the user saves a workbook I want excel to do the following:

1. Unhide sheet1
2. Hide Sheet2
3. Save the file
4. Unhide sheet2
5. Hide sheet1

Up to step 3 is not a problem, but steps 4 and 5 is a problem as I am using the following event:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
and I cannot unhide the sheets after the save.

 

[jimboy]

Try this, the last line will allow you to close the wb without it asking you to save changes;

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = False
ActiveWorkbook.Save
Sheets("Sheet2").Visible = True
Sheets("Sheet1").Visible = False
ThisWorkbook.Saved = True
End Sub

 

[LAS]

The following code work. Application.enableevents = False is needed in order for it to work:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Sheet1.Visible = xlSheetVisible
    Sheet2.Visible = xlSheetVeryHidden
    ActiveWorkbook.Save
    Sheet2.Visible = xlSheetVisible
    Sheet1.Visible = xlSheetVeryHidden
    Me.Saved = True
    Cancel = True
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Thanks for all the help

 

[jimboy]

. Application.enableevents = False is needed in order for it to work:

Why?

 

[Richie(UK)]

Hi Jimboy,

The line isn't needed for the suggested routine to work. However, it would prevent the event being called twice.

Try the following code:

Dim iSaveCount As Integer

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    iSaveCount = iSaveCount + 1
    MsgBox "Hello. Save number : " & iSaveCount
    Me.Save
    iSaveCount = 0
End Sub

Now try it with Events switched off:

Dim iSaveCount As Integer

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    iSaveCount = iSaveCount + 1
    MsgBox "Hello. Save number : " & iSaveCount
    Application.EnableEvents = False
    Me.Save
    Application.EnableEvents = True
    iSaveCount = 0
End Sub

Without events switched off the Me.Save line will call the BeforeSave event a second time.

 

[jimboy]

Thanks.