Before close and workbook open!

  • Thread starter Thread starter Marc
  • Start date Start date
M

Marc

Is this code ok?

Private Sub Workbook_BeforeClose(cancel As Boolean)
Application.ScreenUpdating = False
Worksheets(2).Visible = xlVeryHidden
Worksheets(3).Visible = xlVeryHidden
Worksheets(4).Visible = xlVeryHidden
Worksheets(5).Visible = xlVeryHidden
Application.StatusBar = "Saving..."
ActiveWorkbook.Save
End Sub


Private Sub Workbook_Open()
Application.ScreenUpdating = False
Application.StatusBar = "Loading excel file..."
frmSplash.Show

ActiveWorkbook.Worksheets(1).EnableSelection = xlUnlockedCells
Worksheets(1).Activate
ActiveSheet.Range("C4").Select
Worksheets(2).Visible = True
Worksheets(3).Visible = True
Worksheets(4).Visible = True
Worksheets(5).Visible = True
Application.StatusBar = "Ready"
End Sub

Does anyone have some suggestions, and would this work at excel 97?

Thanks in advance?
 
Tested in Excel97. Works fine, assuming thast there are at least 5
worksheets and that worksheets(1) is not hidden during the session. Nothing
happens until frmSplash is unloaded, guess that's your intention.

I'd change to this to take care of hidden / added sheets:

Private Sub Workbook_BeforeClose(cancel As Boolean)
Dim L As Long
Application.ScreenUpdating = False
Me.Worksheets(1).Visible = True
For L = 2 To Me.Worksheets.Count
Me.Worksheets(L).Visible = xlVeryHidden
Next
Application.StatusBar = "Saving..."
ActiveWorkbook.Save
End Sub

and a similar method in Open.

HTH. Best wishes Harald
 
Back
Top