VB code to hide selected ws

pilot · Apr 5th, 2002, 02:34 PM

WS tab names end in either an alpha character or a 1, 2 or 3. What code (tied to a button) will permit hiding or unhiding of certain sheets such as "unhide all sheets ending in a 2 and hide all others"? How about if I want to just toggle the "hid" condition of all sheets ending in a 2?

daleyman · Apr 5th, 2002, 02:55 PM

Use the following code routines...

Sub hide_pages()
For sheetno = 1 To ActiveWorkbook.Sheets.Count
If Right(Sheets(sheetno).Name, 1) = 2 Then
Sheets(sheetno).Visible = False
Else
Sheets(sheetno).Visible = True
End If
Next
End Sub

Sub toggle_pages()
For sheetno = 1 To ActiveWorkbook.Sheets.Count
If Right(Sheets(sheetno).Name, 1) = 2 Then
If Sheets(sheetno).Visible = True Then
Sheets(sheetno).Visible = False
Else
Sheets(sheetno).Visible = True
End If
End If
Next
End Sub

...substituting the IF clause for whatever you need.

Al Chara · Apr 5th, 2002, 02:56 PM

The following code will hide all worksheets in the current workbook that end in 2:

For i = 1 To ThisWorkbook.Worksheets.Count
If Right(Worksheets(i).Name, 1) = 2 Then Worksheets(i).Visible = False
Next i

Is this what you wanted?

Damon Ostrander · Apr 5th, 2002, 03:18 PM

Hi Pilot,

How about:

Dim Sh As Object
For Each Sh in Sheets
Sh.Visible = Sh.Name Like "*2"
Next Sh

daleyman · Apr 5th, 2002, 03:21 PM

hey damon that's nice. =P

pilot · Apr 8th, 2002, 08:13 AM

Thanks guys, this is getting there. Daleyman, in both of your Subs, the 3rd line caused an abortion (?).

Damon's suggestion works fine but I want to take it a step or two beyond. What is the code to display all sheets like "*1 or *2"?

How about displaying all sheets NOT like "*1 or *2"?

One more, display all sheets like "*2" and the sheet titled "XYZ"?

Damon Ostrander · Apr 8th, 2002, 04:51 PM

Answer to pilot's follow-on question:

Damon's suggestion works fine but I want to take it a step or two beyond. What is the code to display all sheets like "*1 or *2"?

Sh.Visible = Sh.Name Like "*[1-2]"

How about displaying all sheets NOT like "*1 or *2"?

Sh.Visible = Not (Sh.Name Like "*[1-2]")

One more, display all sheets like "*2" and the sheet titled "XYZ"?

Sh.Visible = (Sh.Name Like "*2") Or Sh.Name = "XYZ"

pilot · Apr 9th, 2002, 08:07 AM

Perfect, Damon, exactly what I needed. I very muchappreciate your help.

Apr 5th, 2002, 02:56 PM	#3
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	The following code will hide all worksheets in the current workbook that end in 2: For i = 1 To ThisWorkbook.Worksheets.Count If Right(Worksheets(i).Name, 1) = 2 Then Worksheets(i).Visible = False Next i Is this what you wanted? __________________ Kind regards, Al Chara

Apr 5th, 2002, 03:18 PM	#4
Damon Ostrander MrExcel MVP Join Date: Feb 2002 Location: Denver, Colorado USA Posts: 4,014	Hi Pilot, How about: Dim Sh As Object For Each Sh in Sheets Sh.Visible = Sh.Name Like "*2" Next Sh __________________ Keep Excelling. Damon VBAexpert Excel Consulting (My other life: http://damonostrander.com )

Apr 8th, 2002, 04:51 PM	#7
Damon Ostrander MrExcel MVP Join Date: Feb 2002 Location: Denver, Colorado USA Posts: 4,014	Answer to pilot's follow-on question: Damon's suggestion works fine but I want to take it a step or two beyond. What is the code to display all sheets like "1 or 2"? Sh.Visible = Sh.Name Like "[1-2]" How about displaying all sheets NOT like "1 or 2"? Sh.Visible = Not (Sh.Name Like "[1-2]") One more, display all sheets like "2" and the sheet titled "XYZ"? Sh.Visible = (Sh.Name Like "2") Or Sh.Name = "XYZ" __________________ Keep Excelling. Damon VBAexpert Excel Consulting (My other life: http://damonostrander.com )

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 5th, 2002, 02:34 PM	#1
pilot Board Regular Join Date: Feb 2002 Location: Houston, TX Posts: 303	WS tab names end in either an alpha character or a 1, 2 or 3. What code (tied to a button) will permit hiding or unhiding of certain sheets such as "unhide all sheets ending in a 2 and hide all others"? How about if I want to just toggle the "hid" condition of all sheets ending in a 2?

Apr 5th, 2002, 02:55 PM	#2
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	Use the following code routines... Sub hide_pages() For sheetno = 1 To ActiveWorkbook.Sheets.Count If Right(Sheets(sheetno).Name, 1) = 2 Then Sheets(sheetno).Visible = False Else Sheets(sheetno).Visible = True End If Next End Sub Sub toggle_pages() For sheetno = 1 To ActiveWorkbook.Sheets.Count If Right(Sheets(sheetno).Name, 1) = 2 Then If Sheets(sheetno).Visible = True Then Sheets(sheetno).Visible = False Else Sheets(sheetno).Visible = True End If End If Next End Sub ...substituting the IF clause for whatever you need.

Apr 5th, 2002, 03:21 PM	#5
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	hey damon that's nice. =P

Apr 8th, 2002, 08:13 AM	#6
pilot Board Regular Join Date: Feb 2002 Location: Houston, TX Posts: 303	Thanks guys, this is getting there. Daleyman, in both of your Subs, the 3rd line caused an abortion (?). Damon's suggestion works fine but I want to take it a step or two beyond. What is the code to display all sheets like "1 or 2"? How about displaying all sheets NOT like "1 or 2"? One more, display all sheets like "*2" and the sheet titled "XYZ"?

Apr 9th, 2002, 08:07 AM	#8
pilot Board Regular Join Date: Feb 2002 Location: Houston, TX Posts: 303	Perfect, Damon, exactly what I needed. I very muchappreciate your help.