VB code to hide selected ws
VB code to hide selected ws
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: VB code to hide selected ws

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    334
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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?

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  4. #4
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hey damon that's nice. =P

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    334
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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"?

  7. #7
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    334
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com