CODEWITHSUNDEEP
excelvba
Mohsen
Mohsen

Reputation: 175

Define sheets and worksheets in VBA

My workbook has both chart sheets and normal sheets, therefore I am using Sheets instead of Worksheets. However, I don't know what the type of sht should be in the following set of codes. I am sure it cannot be Worksheet.

' Hide all sheets/charts except Sheet1
Sub Hide_Sheets()

    Dim sht As ???

    For Each sht In ActiveWorkbook.Sheets
        If sht.Name <> Sheet3.Name Then
            sht.Visible = False
        End If
    Next sht

End Sub

Upvotes: 1

Views: 1229

Answers (2)

Jon Crowell
Jon Crowell

Reputation: 22358

"Charts and Worksheets are two different collections." --> https://stackoverflow.com/a/6804704/138938

If you have both chart sheets and regular worksheets, you can either loop through a collection of objects like this:

Sub Hide_Objects()
    Dim wb As Workbook
    Dim obj As Object

    Set wb = ActiveWorkbook

    For Each obj In wb.Sheets
        If obj.Name <> "Sheet1" Then
            obj.Visible = False
        End If
    Next obj
End Sub

Or you can loop through both collections like this:

Sub Hide_Sheets_And_Charts()
    Dim wb As Workbook
    Dim sht As Worksheet
    Dim cht As Chart

    Set wb = ActiveWorkbook

    For Each sht In wb.Worksheets
        If sht.Name <> "Sheet1" Then
            sht.Visible = False
        End If
    Next sht

    For Each cht In wb.Charts
        If cht.Name <> "Sheet1" Then
            cht.Visible = False
        End If
    Next cht

End Sub

Upvotes: 4

Keith John Hutchison
Keith John Hutchison

Reputation: 5287

Use Variant then step through the code and you'll be able to see what it is. Variant will work without doing anything else.

I recommend using real names for variables to make it easier for you to read your code at some stage in the future. 

Sub Hide_Sheets()

    Dim sheet_ As Variant

    For Each sheet_ In ActiveWorkbook.Sheets
        If sheet_.Name <> Sheet3.Name Then
            sheet_.Visible = False
        End If
    Next sheet_

End Sub

Upvotes: 1

Related Questions