Hiding sheets

LAS

Board Regular
Joined
Oct 29, 2002
Messages
215
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.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

jimboy

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
Try this, the last line will allow you to close the wb without it asking you to save changes;

Code:
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

Board Regular
Joined
Oct 29, 2002
Messages
215
The following code work. Application.enableevents = False is needed in order for it to work:

Code:
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
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
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:
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:
Code:
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,871
Messages
5,766,855
Members
425,380
Latest member
CubeGirl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top