macro to hide sheets

  • Thread starter Thread starter EricBB
  • Start date Start date
E

EricBB

why the below is not working? pls. help...

Private Sub CommandButton2_Click()
For i = 1 To Worksheets.Count
If Not (Worksheets(i).CodeName = "Sheet1" Or "Sheet5" Or "Sheet9") Then
Sheets(i).Visible = False
Next
Sheet1.Select
End Sub
 
why the below is not working? pls. help...

Private Sub CommandButton2_Click()
For i = 1 To Worksheets.Count
If Not (Worksheets(i).CodeName = "Sheet1" Or "Sheet5" Or "Sheet9") Then
Sheets(i).Visible = False
Next
Sheet1.Select
End Sub
Click to expand...

You're missing an EndIf (or an underscore after Then to create a line
break) and you can't do a comparison like that.

Also, I'm assuming that you want sheets 1m 5 and 9 NOT hidden given
that you try to select sheet1 at the end, which you can't do if it's
hidden.

One way of doing it, as close to your original one as possible:

Private Sub CommandButton2_Click()
Dim i As Integer
Dim s As String

For i = 1 To Worksheets.Count

s = Worksheets(i).CodeName

If (s <> "Sheet1" And _
s <> "Sheet5" And s <> "Sheet9") Then _

ThisWorkbook.Worksheets(i).Visible = xlSheetHidden

Else

ThisWorkbook.Worksheets(i).Visible = xlSheetVisible

End If

Next

Sheet1.Select
End Sub
 
You can't make all sheets invisible. First:

Sub dural()
For Each ws In Worksheets
ws.Visible = True
Next
End Sub

and then:

Private Sub CommandButton2_Click()
Dim sh As Worksheet, n As String
For i = 1 To Worksheets.Count
Set sh = Worksheets(i)
n = sh.Name
If Not (n = "Sheet1" Or n = "Sheet5" Or n = "Sheet9") Then
sh.Visible = False
End If
Next
End Sub
 
When you use OR in VBA; it should be in the format

(criteria = value1 Or criteria=value2 Or criteria=value3)

If this post helps click Yes
 
Sometimes, If/then statements can get unruly with lots of criteria.

Private Sub CommandButton2_Click()
dim i as long
For i = 1 To Worksheets.Count
select case lcase(worksheets(i).codename)
case is = "sheet1","sheet5","sheet9" 'all lower case!
'do nothing
case else
sheets(i).visible = xlsheethidden
end select
next i
Sheet1.Select
End Sub
 
Sometimes, If/then statements can get unruly with lots of criteria.
Click to expand...

Agreed; I considered ptting a Select Case in my own response but
decided to keep it to as close to the original code as possible.
Select Case is still preferred though.
 
Back
Top