[RESOLVED] Hiding sheets based on their Tab name

Printable View

Aug 26th, 2009, 10:37 PM
tome10

[RESOLVED] Hiding sheets based on their Tab name

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.

Re: Hiding sheets based on their Tab name

Try this

vb Code:

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

Aug 31st, 2009, 06:29 PM
tome10

Re: Hiding sheets based on their Tab name

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?
Sep 1st, 2009, 02:07 AM
Siddharth Rout

1 Attachment(s)

Re: Hiding sheets based on their Tab name

Quote:

I guess a Match Case Scenario.

No you don't need that

Something like this perhaps...
Sep 8th, 2009, 08:05 PM
tome10

Re: Hiding sheets based on their Tab name

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?
Sep 9th, 2009, 02:23 AM
Siddharth Rout

Re: Hiding sheets based on their Tab name

Quote:

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...
Sep 9th, 2009, 08:55 PM
tome10

Re: Hiding sheets based on their Tab name

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.
Sep 11th, 2009, 10:12 PM
tome10

Re: Hiding sheets based on their Tab name

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.

HTML Code:

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
Sep 12th, 2009, 07:29 PM
westconn1

Re: Hiding sheets based on their Tab name
to toggle between the last 2 sheets is very easy on a toolbar button, just use code like
vb Code:
```
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
vb Code:
```
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
vb Code:
```
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
Sep 12th, 2009, 07:50 PM
tome10

Re: Hiding sheets based on their Tab name

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.
Sep 12th, 2009, 07:55 PM
westconn1

Re: Hiding sheets based on their Tab name
yeah that is easy to do in the sheet activate event move the sheet
vb Code:
```
Application.ScreenUpdating = False
Sh.Move Sheets(1)
```
Sep 13th, 2009, 06:38 AM
tome10

Re: Hiding sheets based on their Tab name

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?

Code:

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
Sep 13th, 2009, 07:21 AM
westconn1

Re: Hiding sheets based on their Tab name

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

Quote:

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
Sep 13th, 2009, 03:44 PM
tome10

Re: Hiding sheets based on their Tab name

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.

Code:

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
Sep 13th, 2009, 04:13 PM
westconn1

Re: Hiding sheets based on their Tab name

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
Sep 14th, 2009, 09:15 PM
tome10

Re: Hiding sheets based on their Tab name

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
Sep 14th, 2009, 09:35 PM
tome10

Re: Hiding sheets based on their Tab name

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.

Code:

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
Sep 14th, 2009, 10:26 PM
westconn1

Re: Hiding sheets based on their Tab name

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
Sep 14th, 2009, 11:49 PM
tome10

Re: Hiding sheets based on their Tab name

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

Code:

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
Sep 15th, 2009, 05:42 AM
westconn1

Re: Hiding sheets based on their Tab name

read more carefully my previous post

Quote:

the if sheets(i) part, must be inside the for loop
Sep 15th, 2009, 06:32 AM
Siddharth Rout

Re: Hiding sheets based on their Tab name

Quote:

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...
Sep 15th, 2009, 02:21 PM
tome10

Re: Hiding sheets based on their Tab name

ok, I have everything working except the History part.

Code:

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.
Sep 15th, 2009, 02:33 PM
tome10

Re: Hiding sheets based on their Tab name

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?
Sep 15th, 2009, 04:42 PM
Siddharth Rout

1 Attachment(s)

Re: Hiding sheets based on their Tab name

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?
Sep 15th, 2009, 08:42 PM
tome10

Re: Hiding sheets based on their Tab name

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.
Sep 16th, 2009, 01:41 AM
Siddharth Rout

Re: Hiding sheets based on their Tab name

Would it okay if you could select which sheets not to hide and which one to hide?
Sep 16th, 2009, 06:57 AM
tome10

1 Attachment(s)

Re: Hiding sheets based on their Tab name

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.

All times are GMT -5. The time now is 06:33 AM.