macro to hide sheets
 

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

macro to hide sheets
 

On Mon, 10 Aug 2009 01:43:01 -0700, EricBB
wrote:

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

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



---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is {Between permanent e-mail addresses at this time, which makes the next line of my signature redundant}
* Please keep all replies in this Newsgroup. Thanks! *

macro to hide sheets
 

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
--
Gary''s Student - gsnu200860


"EricBB" wrote:

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

macro to hide sheets
 

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
---------------
Jacob Skaria


"EricBB" wrote:

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

macro to hide sheets
 

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

EricBB wrote:

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

--

Dave Peterson

macro to hide sheets
 

On Mon, 10 Aug 2009 07:46:34 -0500, Dave Peterson
wrote:

Sometimes, If/then statements can get unruly with lots of criteria.

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.

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

EricBB wrote:

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