VBA help needed please

[Dougie1]

Hi all -

Using Excel 2007.

Can anyone help me modify the code below?

What it does on workbook close is hide all worksheets except Sheet1 & Sheet2.

I have recently added a Chart, but on close the code leaves it visible along with sheets 1 & 2.

I know it is something to do with the chart not being a worksheet, but am at a loss how to include it in this code. All help gratefully received.

TIA

D

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ws As Worksheet
    Application.Calculation = xlAutomatic
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Sheet1" And ws.Name <> "Sheet2" Then ws.Visible = xlSheetVeryHidden
    Next ws
End Sub

 

[Sektor]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    Dim obj As Object, sh As Worksheet
    
    Application.Calculation = xlAutomatic
    
    For Each obj In Sheets
        If TypeName(obj) = "Worksheet" Then
            Set sh = obj
            If sh.Name <> "Sheet1" And sh.Name <> "Sheet2" Then
                sh.Visible = xlSheetVeryHidden
            End If
        End If
    Next

End Sub

 

[Dougie1]

Thanks Sektor but that is now generating an error at second last line :

Next ws

 

[Dougie1]

Sorry Sektor - it is NOT generating an error at that line - I missed some code while copying over.

I still have a problem though - the code is not hiding the chart sheet, leaving it still visible alongside sheets 1 & 2.

Any help appreciated

 

[SillyCat]

Stealing Sektor's thunder this word version will do it:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    Dim obj As Object, sh As Worksheet
    
    Application.Calculation = xlAutomatic
    
    For Each obj In Sheets
        If TypeName(obj) = "Worksheet" Then
            Set sh = obj
            If sh.Name <> "Sheet1" And sh.Name <> "Sheet2" Then
                sh.Visible = xlSheetVeryHidden
            End If
        End If
        
        If TypeName(obj) = "Chart" Then
            obj.Visible = xlSheetVeryHidden
        End If
    Next

End Sub

Or shorter less legible:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    Dim obj As Object
    
    Application.Calculation = xlAutomatic
    
    For Each obj In Sheets
        If TypeName(obj) <> "Worksheet" Or (obj.Name <> "Sheet1" And obj.Name <> "Sheet2") Then
                obj.Visible = xlSheetVeryHidden
        End If
    Next

End Sub

 

[Dougie1]

Brilliant - thanks for all your help, SillyCat and Sektor.

Much appreciated.