Solved: Hide Unhide Worksheets based on Name

[xluser2007]

Hi All,

I have attached a sample workbook.

It contains the following 4 sheets:

Parameters OPEN by TYPE OPEN by AGENT MODIFY by TYPE MODIFY by AGENT
I want to have 3 buttons in the Parameters tab.

1. One button to hide all worksheets that contain the string "TYPE"
2. One button to hide all worksheets that contain the string "AGENT"
3. One button to UNHIDE all worksheets that contain the strings "AGENT" or "TYPE".

the string matches should not be case sensitive.

I've attached a sample workbook to test and write the code.

Any help is appreciated to write this code.

[xluser2007]

Please find attached the sample workbook.

[GTO]

Greetings,

Presuming I got the buttons right, here would be one way... Please note the Option Compare Text at the top of the sheet's module to keep the comparison case-insensitive.

In the Worksheet Module (the sheet w/the buttons) :

[vba]
Option Explicit
Option Compare Text

Private Sub CommandButton1_Click()
'by Type and Agent
SheetVisibility_SET xlSheetVisible, "AGENT", "TYPE"
End Sub

Private Sub CommandButton2_Click()
'by Agent
SheetVisibility_SET xlSheetVeryHidden, "AGENT"
End Sub

Private Sub CommandButton3_Click()
'by Type
SheetVisibility_SET xlSheetVeryHidden, "TYPE"
End Sub

Private Sub SheetVisibility_SET(SetVis As XlSheetVisibility, ParamArray ShPartName() As Variant)
Dim wks As Worksheet
Dim i As Long
Dim strLike As String

For i = LBound(ShPartName) To UBound(ShPartName)
strLike = "*" & ShPartName(i) & "*"

For Each wks In ThisWorkbook.Worksheets
If wks.Name Like strLike Then wks.Visible = SetVis
Next
Next
End Sub
[/vba]

Hope that helps,

Mark

[xluser2007]

Hi Mark,

many thanks as always for your great efforts. Your code is always a pleasure to learn from, i especially like the way you used Paramarray.

I pasted it in the sheet module as you suggested, please see the revised workbook.

A few queries though:

1. If I hit Open by Agent, it hides the AGENT sheets instead of displaying them, so fixing this is simply switching the command button2 and 3 codes around.
2. However if i hit Open by AGENT, and then hit open by TYPE, it actually hides them both. I was hoping that it would always show the Worksheet names that I last clicked.

That is the OPEN by TYPE and AGENT Button works beatuifully as it shows both worksheets that have AGENT and TYPE.

I am thinking that it would probably be best to combine Command buttons 2 and 3 whereby the caption changes depending on what you have displayed.

For example Commandbutton1 would stay as is.

Then there would be a second ToggleCommandButton_Click, which would have the caption say "OPEN by TYPE" and all the AGENT sheets would be OPEN. Afterb you CLICKED it, it would hide all the AGENT sheets and display all the TYPE sheets, the caption would then change to "OPEN by AGENT". is this possible to do?

Thank you sincerely for your help thus far, it is almost there.

[xluser2007]

The revised workbooks is attached with your new code.

[GTO]

Hi xluser,

I thought I might be a tad off from your first post.

I want to have 3 buttons in the Parameters tab.

1. One button to hide all worksheets that contain the string "TYPE"
2. One button to hide all worksheets that contain the string "AGENT"
3. One button to UNHIDE all worksheets that contain the strings "AGENT" or "TYPE".

If we were to re-state this, would it be accurate to say:

1. When button 'Open by Agent" is clicked, we should display all the sheets with "Agent" in the tab name, and hide all sheets with "Type" in the name.
2. When button "Open by Type" is clicked, we should display all the sheets with "Type" in the name and hide all the sheets with "Agent" in the tab name.
3. When button "Open by Type and Agent" is clicked, all these sheets should be made visible.

Mark

[xluser2007]

Hi xluser,

I thought I might be a tad off from your first post.



If we were to re-state this, would it be accurate to say:

1. When button 'Open by Agent" is clicked, we should display all the sheets with "Agent" in the tab name, and hide all sheets with "Type" in the name.
2. When button "Open by Type" is clicked, we should display all the sheets with "Type" in the name and hide all the sheets with "Agent" in the tab name.
3. When button "Open by Type and Agent" is clicked, all these sheets should be made visible.

Mark

Hi Mark,

That's precisely correct.

Thanks again for your interest

[GTO]

Okay, this won't be as efficient as possible, but presuming there aren't a horrid amount of sheets, calling the function twice seems harmless to me.

I also added a toggle button currently named "ToggleButton1" to answer your question about that.

[vba]
Option Explicit
Option Compare Text

Private Sub CommandButton1_Click()
'by Type and Agent
Call SheetVisibility_SET(xlSheetVisible, "AGENT", "TYPE")
End Sub

Private Sub CommandButton2_Click()
'by Agent

'// Just call the function twice, first setting AGENT sheets to visibe, then //
'// again to make make TYPE sheets hidden. //
'// I would note that I most always try and do this in this order to prevent //
'// ever accidently trying to make all sheets hidden, which will fail. //
Call SheetVisibility_SET(xlSheetVisible, "AGENT")
Call SheetVisibility_SET(xlSheetVeryHidden, "TYPE")
End Sub

Private Sub CommandButton3_Click()
'by Type
Call SheetVisibility_SET(xlSheetVisible, "TYPE")
Call SheetVisibility_SET(xlSheetVeryHidden, "AGENT")
End Sub

Private Sub SheetVisibility_SET(SetVis As XlSheetVisibility, _
ParamArray ShPartName() As Variant)
Dim wks As Worksheet
Dim i As Long
Dim strLike As String

For i = LBound(ShPartName) To UBound(ShPartName)
strLike = "*" & ShPartName(i) & "*"

For Each wks In ThisWorkbook.Worksheets
If wks.Name Like strLike Then wks.Visible = SetVis
Next
Next
End Sub

Private Sub ToggleButton1_Click()

With Me.ToggleButton1
If .Value Then
.Caption = "OPEN by TYPE"
Call SheetVisibility_SET(xlSheetVisible, "AGENT")
Call SheetVisibility_SET(xlSheetVeryHidden, "TYPE")
Else
.Caption = "OPEN by AGENT"
Call SheetVisibility_SET(xlSheetVisible, "TYPE")
Call SheetVisibility_SET(xlSheetVeryHidden, "AGENT")
End If
End With
End Sub
[/vba]

Mark

[xluser2007]

Mark,

You are a real trooper.

Aside from the Toggle button that works brilliantly indeed , thank you sincerely. And I'm happy to have 3 buttons instead of 2, makes it more explicit.

Just for curiosity for the ToggleButton Approach though, I've attached a sample workbook, any ideas where i may have mispplied somehow?

regards

[GTO]

Heyyyy, yer trying to confuse the blonde guy! (which is too easy to do anyway)

Your "togglebutton" is a regular command button!

Mark

PS. I edited my last post and the code therein a tiny bit. Please use the newer, just so as we stay on same page. No changes of substance, just made the calls more noticeable.

[xluser2007]

Heyyyy, yer trying to confuse the blonde guy! (which is too easy to do anyway)

Your "togglebutton" is a regular command button!

Hi Mark, sorry I'm confused now .

Should I have not created the Togglebutton the way that I have? Sorry i must have misunderstood the last part of your code.

[GTO]

Sorry, I thought you just selected the wrong control in haste. I assume you have the control toolbox showing in order to create the command button. The TogglButton control is this one:

[xluser2007]

Sorry, I thought you just selected the wrong control in haste. I assume you have the control toolbox showing in order to create the command button. The TogglButton control is this one:

Thanks Mark, that's great!

I didn't even know that ToggleButton's are a separate Control.

I thought they were just a clever manipulations of the Commandbutton Control.

Thanks for letting me know. this thread has taught me a more than just hiding and Unhiding sheets .

Thread marked as Solved.

regards

[GTO]

You are most welcome; I am certainly happy to help.

By the way, a little thing that you may well already be aware of, but in case not...

If you tack in a module level Boolean, you can stop the file from marking itself as unsaved ea time a button is clicked. This stops the user from being asked if he wants to save upon exit, if the only thing they've done (since last saving) is to click a button or two...

[vba]
Option Explicit
Option Compare Text
Dim bolSaved As Boolean

Private Sub CommandButton1_Click()
'by Type and Agent

bolSaved = ThisWorkbook.Saved
Call SheetVisibility_SET(xlSheetVisible, "AGENT", "TYPE")
ThisWorkbook.Saved = bolSaved
End Sub
[/vba]

Just thought to mention in case handy


Have a great afternoon,

Mark

[xluser2007]

Thanks Mark, that is handy to know.

Would I add those lines in each of the commandbuttons then (aside from the top worksheet module level Boolean variable)?

i.e. the bold lines

[vba]Option Explicit
Option Compare Text
Dim bolSaved As Boolean

Private Sub CommandButton1_Click()
'by Type and Agent

bolSaved = ThisWorkbook.Saved
Call SheetVisibility_SET(xlSheetVisible, "AGENT", "TYPE")
ThisWorkbook.Saved = bolSaved
End Sub

' And then add them to the next command button

Private Sub CommandButton2_Click()
'by Agent

bolSaved = ThisWorkbook.Saved
Call SheetVisibility_SET(xlSheetVisible, "AGENT")
Call SheetVisibility_SET(xlSheetVeryHidden, "TYPE")
ThisWorkbook.Saved = bolSaved

End Sub

' and then add to the next Command Button etc [/vba]

[GTO]

Yes, just like that.

Just remember that if it was for a commandbutton (or whatever type control) that actually changed something (like entered or updated values or fromulas), then we'd want the user to be warned about closing without saving. In this case though, as we're just hiding/showing some sheets, we can avoid some confusion for the user, who is wondering what in the heck he/she changed.

Mark

[xluser2007]

Yes, just like that.

Just remember that if it was for a commandbutton (or whatever type control) that actually changed something (like entered or updated values or fromulas), then we'd want the user to be warned about closing without saving. In this case though, as we're just hiding/showing some sheets, we can avoid some confusion for the user, who is wondering what in the heck he/she changed.

Mark

Thanks Mark, that helps indeed .

[xluser2007]

Hi Mark,

Just a follow up query.

Dow do we add "hover text"to a Commandbutton i.e. wehn the user moves their mouse ever the Commandbutton named "OPEN_by_TYPE_and_AGENT_Click", I would like hover text to be displayed that states the following:

"Would you like to SHOW Only worksheets in this workbook by TOOL TYPE and AGENT name"

Any ideas on how to do this with say the example workbook _v3 attached above?

[xluser2007]

I tried the following:

[VBA]Option Explicit
Option Compare Text

Private Sub OPEN_by_TYPE_and_AGENT_Click()

'by TYPE and AGENT
Call SheetVisibility_SET(xlSheetVisible, "AGENT", "TYPE")

End Sub

Sub OPEN_by_TYPE_and_AGENT_MouseOver()
Me.Caption = "SHOW Only worksheets in this workbook by TOOL TYPE and AGENT name"
End Sub[/VBA]

But it threw an "Compile error: Member or data member not found" and highlighted the code

.Caption =

Any ideas how to modify the above or any alternatives I can use

[xluser2007]

I tried the following:

[vba]Option Explicit
Option Compare Text

Private Sub OPEN_by_TYPE_and_AGENT_Click()

'by TYPE and AGENT
Call SheetVisibility_SET(xlSheetVisible, "AGENT", "TYPE")

End Sub

Sub OPEN_by_TYPE_and_AGENT_MouseOver()
Me.Caption = "SHOW Only worksheets in this workbook by TOOL TYPE and AGENT name"
End Sub[/vba]
But it threw an "Compile error: Member or data member not found" and highlighted the code

.Caption =

Any ideas how to modify the above or any alternatives I can use?