[RESOLVED] Hiding sheets based on their Tab name

[tome10]

I have a workbook with a LOT of sheets, and they are named by SITECODE-NENUMBER
"ATGXGA-000x" 1 though 24
"ATFIGA-000x" 1 through 12
etc.etc
"there are lots more"
Is there a way to hit a botton Labeled ATGXGA and hide all the others? I would want it to just look at the SITECODE

ohh and I don't mean to have to list them all and put visible = true
I do that now, and have to go in and edit the Macro when I add another node.

[Siddharth Rout]

Try this

Sub HideSheets()
    Dim intCount As Integer
    For i = 1 To Sheets.Count
        If Left(Trim(Sheets(i).Name), 6) = "ATGXGA" Then
            intCount = intCount + 1
            '~~> Error Check : If all sheets in the workbook have
            '~~> names starting with "ATGXGA"
            If intCount = Sheets.Count Then
                MsgBox "You cannot hide all sheets. You need to keep at least 1 sheet visible."
                Exit Sub
            End If
            Sheets(i).Visible = False
        End If
    Next
End Sub

[tome10]

Ok, the above code works, but before I mark it Resolved I would like to work on a varient for a different workbook that is not as easy to organize.
I have a code that inventories the sheets names in the workbook Sheet Index B1. I want to make a drop down list in say G9 with this info and whatever is selected in the Drop down list is shown and everything else is hidden.
I guess a Match Case Scenario. Should I open a new thread with this question?

[Siddharth Rout]

I guess a Match Case Scenario.

No you don't need that

Something like this perhaps...

[tome10]

Thanks Sid, that works. But, I have to ask. Is there a way to have a lagging history kinda thing? say the last 3, 4, or 5sheets selected be visible?

[Siddharth Rout]

Is there a way to have a lagging history kinda thing? say the last 3, 4, or 5sheets selected be visible?

I am sorry I don't understand. Could you explain this a little more...

[tome10]

Ok, I have a workbook with like 20 sheets named various things. The last varient of this code has a drop down list that shows what is currently selected in the list, and everything else is hidden. Ok, I'm thinking there is no way to easily toggle between 2 or 3 sheets maybe doing some copy and pasting between them. So I am wondering if there is a way to set up to where the last few 2-4 sheets selected in the list be visible. So I always have a certain number of Worksheets Visible. Being the last sheets viewed/activated. Say I have this history number set at 3. sheet1, sheet2, sheet3 are visible, these sheets are visible because 1st I chose sheet1 in the drop down, and 2nd I chose sheet2, and 3rd I chose sheet3. Now the 4th time I select say sheet4 in the drop down, and sheet4 is visible, and sheet1 becomes hidden again. etc.etc.

[tome10]

How and where would I write a
IF "F3" = "ALL" show all in this code? But I still would like to have the lagging thing. It's kinda a pain to switch between sheets.

Private Sub CommandButton1_Click()
    If Len(Trim(Range("F3"))) = 0 Then
        MsgBox "Please Select a Sheet name"
        Exit Sub
 
    End if
    For i = 1 To Sheets.Count
        If Sheets(i).Name <> "Index" Then Sheets(i).Visible = False
    Next i
    Sheets(Trim(Range("F3"))).Visible = True
End Sub

[westconn1]

to toggle between the last 2 sheets is very easy on a toolbar button, just use code like

oldname = activesheet.name
sheets(button.caption).activate
button.caption = oldname

this does not updat the sheet to change to if a sheet is as actrivated by some other method

alternatively you can update listbox from the worksheet

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
With Sheet1.ListBox1
l = .ListCount - 1
    If l > 2 Then .RemoveItem (l)
    .AddItem Sh.Name, 0
End With
End Sub

of course this in not very useful if the listbox is on a sheet as you need it to always be ontop, but you can add a combo to a toolbar

Set s = Application.CommandBars.Add
s.visible = true
Set combo = s.Controls.Add(msoControlComboBox)

declare combo at form level (in the general section)
replace code sheet1.listbox1 with combo
also change to .additem sh.name, 1

[tome10]

I was thinking by way of sorting the sheets. I've seen codes where people move the tabs infront of other sheets. maybe something like that where the first 3-4 sheets are not hidden and the next sheet activated is shuffled to the front.

[westconn1]

yeah that is easy to do in the sheet activate event move the sheet

Application.ScreenUpdating = False
Sh.Move Sheets(1)

[tome10]

West, can you Proof read this? Also, I put a move sheet at the end. Do you know how to code when it is looping through the Sheets to disregard (not hide) the first certain number of sheets?

 
Private Sub CommandButton1_Click()
    If Len(Trim(Range("F3"))) = 0 Then
        MsgBox "Please Select a Sheet name"
    If  Len(Trim(Range("F3"))) = "ALL" Then
     For i = 1 To Sheets.Count
        Sheets(i).Visible = True   
    Exit Sub 'Should I have 2 End If Statements before this Exit Sub?
    
    End if   ' don't know why this is here
    For i = 1 To Sheets.Count
        If Sheets(i).Name <> "Index" Then Sheets(i).Visible = False
    Next i
    Sheets(Trim(Range("F3"))).Visible = True
    Application.ScreenUpdating = False
    Sh.Move Sheets(1)
End Sub

[westconn1]

If Sheets(i).Name <> "Index" or i < 4 Then Sheets(i).Visible = False

Sh.Move Sheets(1)

sh is not a valid object in this procedure

your exit sub will exectute on the first iteration of the loop so nothing else will happen, you should have next before
i am not sure when you want to exit sub so hard to say where it should go

[tome10]

I can't get this to work. "Not surprising". All I can get it to do is pop up the Message Box if there is nothing in the listbox.

Private Sub CommandButton1_Click()
    If Len(Trim(Range("F3"))) = 0 Then
        MsgBox "Please Select a Sheet name"
    End If
    If Len(Trim(Range("F3"))) = "ALL" Then
     For i = 1 To Sheets.Count
        Sheets(i).Visible = True
        Next i
    End If
    Exit Sub
    
    
        If Sheets(i).Name <> "Index" Or i < 4 Then Sheets(i).Visible = False
    For i = 1 To Sheets.Count
    Next i
    Sheets(Trim(Range("F3"))).Visible = True
    Application.ScreenUpdating = False
    Sheets.Move Sheets(1)
    
    
    
End Sub

[westconn1]

the first part should work to make all sheets visible, except the length of any cell content can never = "ALL"

should be i > 4, i got it right first then changed

sheets.move must specify which sheet to move

[tome10]

I didn't even consider what the LEN meant. I thought it was taking away any spaces. If I was writing it in Embed Formula I would write =IF(F1<>"","messagebox","donext")
I did it! I made all the sheets to show up! I just got rid of all the LEN, TRIM stuff.
If Range("F3") = "ALL" Then

[tome10]

Ok, I can get the Message Box to show up if nothing is in the box, and I can get all the Sheets to show up with the "ALL" function. But.
I can't get the rest of it to do anything.
My thinking is. Take the Sheet that is in the F3 box and move it to the front of the workbook all the way to the Left, but After the Index page. But it does this (looks for the sheet in F4) after it ignores the first 4 sheets in the workbook. That way I always have the last 4 sheets selected visible.

Private Sub CommandButton1_Click()
    If Len(Trim(Range("F3"))) = 0 Then
        MsgBox "Please Select a Sheet name"
    End If
    If Range("F3") = "ALL" Then
     For i = 1 To Sheets.Count
        Sheets(i).Visible = True
        Next i
    End If
    
        If Sheets(i).Name <> "Index" Or i < 4 Then
    Sheets(i).Visible = False
    For i = 1 To Sheets.Count
    Next i
    Sheets(Trim(Range("F3"))).Visible = False
    'Application.ScreenUpdating = False
    'Sheets.Move Sheets(1)
    
    
  End If
End Sub

[westconn1]

trim removes spaces before and after text

to move the sheet to 2nd place (after index sheet)
activesheet.move sheets(2)
is is also possible the code could make the activesheet hidden, so it should be set to visible specifically

the if sheets(i) part, must be inside the for loop
any sheet name in F3 will be hidden

application.screenupdating should be at the begining of the code

if nothing is in F3 after the messagebox the code will continue anyway, you should have exit sub immediately after the msgbox

[tome10]

ok, I made some changes, and it hangs up. run time error 9 subscript out of range. Here:
If Sheets(i).Name <> "Index" Or i < 4 Then

Private Sub CommandButton1_Click()
    'Application.ScreenUpdating = False
    If Len(Trim(Range("F3"))) = 0 Then
        MsgBox "Please Select a Sheet name"
    Exit Sub
    End If
    If Range("F3") = "ALL" Then
     For i = 1 To Sheets.Count
        Sheets(i).Visible = True
        Next i
        Exit Sub
      End If
      
     If Sheets(i).Name <> "Index" Or i < 4 Then
     For i = 1 To Sheets.Count
    Sheets(i).Visible = False
    Next i
    Sheets(Trim(Range("F3"))).Visible = True
    ActiveSheet.Move Sheets(2)
    
  End If
End Sub

[westconn1]

read more carefully my previous post

the if sheets(i) part, must be inside the for loop

[Siddharth Rout]

So I am wondering if there is a way to set up to where the last few 2-4 sheets selected in the list be visible. So I always have a certain number of Worksheets Visible.

There is one way to do it... In the workbook open event, declare an Array

Dim MyArray(2)

And in the the worksheet change event, you can store the names of the last sheet opened.

Logic: For ex: if the last 3 sheets that were opened were, Sheet4, Sheet7 and Sheets 8 then the array will have values like this

Ar(0) = "Sheet4"
Ar(1) = "Sheet7"
Ar(2) = "Sheet8"

So next time if you select Sheet2 then the values will move UP in the array for example

Ar(0) = "Sheet7"
Ar(1) = "Sheet8"
Ar(2) = "Sheet2"

Now all you need to do is simply show these names in the list

Give it a try... If you get stuck, simply post the code that you have tried and we will surely help...

[tome10]

ok, I have everything working except the History part.

For i = 1 To Sheets.Count
     If Sheets(i).Name <> "Index" Or i > 4 Then Sheets(i).Visible = False
    Next i
    Sheets(Trim(Range("F3"))).Visible = True
    'Application.ScreenUpdating = False
    'ActiveSheets.Move Sheets(2)

When I remove the " ' " from the Activesheets.Move Sheets(2) it errors. Runtime error 424 object required.
I changed the Activesheets.move to Activesheet.move it doesn't error anymore, and basically acts the same as not having the command in at all.
But honestly, I don't think the "Or i > 4" part is working.
it is supposed to skip the first 4 sheets in the workbook and then start counting. Which should leave those sheets always visible.
I have changed it to "Or i<4" and still nothing. I think it should be "Or i>4" but either way it isn't working as intended.

[tome10]

Sid, I put Dim MyArray(2) in the Workbook Module Open event, and I created a worksheet change event. But I'm not sure what I put in the Worksheet change event. I put this there.
Ar(0) = "Sheet4"
Ar(0) = "Sheet7"
Ar(0) = "Sheet8"
But I don't think that is what you meant. can you clarify? and are you sure the ignore sheets method I was attempting wouldn't work?

[Siddharth Rout]

Ok Here is an example

The cell D7 in Sheet1 will populate the names of the last 3 sheets you visited. Of course, the last name will be the name of the current sheet that you are in. If you click the D7 cell the first time, you will not see any thing but after you go to other sheets and come back you will get a dropdown with the name of the sheets that you visited.

Is this what you wanted?

[tome10]

Sid, I took a look at that example, and no that's not quite right. I appreciate your help though. I made an Index sheet, that has a list of all the sheets in the Workbook in Col A. I just made a drop down list in the middle of the page (like you did in the Example), and made acquired a code that when the Drop down is empty it comes up with a box saying pick a sheet. I finally got an all command that when "ALL" is in the Drop down ALL sheets in the Workbook are Visible. And Finally, whatever sheet is in the Drop down is Visible and all others Except the Index (Index is always shown) is Hidden. So now I have where only one sheet is shown at a time. I am trying to figure out how to have 4 sheets shown at a time being the last 4 sheets selected in the Drop Down. My thinking was to have the Macro ignore the first 4 sheets in the workbook (Left to Right Index being the First sheet) Sheets2,3,4,5 are ignored when the Macro goes through it's code. Every sheet that is in the Drop Drop down and is Selected Visible is Moved to the Sheet2 slot. So that gives me my history of 4 Sheets Visible.

[Siddharth Rout]

Would it okay if you could select which sheets not to hide and which one to hide?

[tome10]

I don't think I want to do that because occasionally I have to copy a sheet and give it a new Sheet Name, and I don't want to have to go in and edit the Macro every time. There's a lot of sheets. The one at a time thing is way better than having all sheets in the Workbook Visible and having to tab through them to find the one you want to work with, but easily tabbing through the last 4-5 sheets would be awesome. I am attaching my Workbook.