PDA

View Full Version : Solved: Hide Unhide Worksheets based on Name



xluser2007
06-09-2009, 05:17 PM
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.


One button to hide all worksheets that contain the string "TYPE"
One button to hide all worksheets that contain the string "AGENT"
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
06-09-2009, 05:18 PM
Please find attached the sample workbook.

GTO
06-09-2009, 06:18 PM
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) :


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


Hope that helps,

Mark

xluser2007
06-09-2009, 06:41 PM
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:
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.
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
06-09-2009, 06:41 PM
The revised workbooks is attached with your new code.

GTO
06-09-2009, 07:31 PM
Hi xluser,

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


I want to have 3 buttons in the Parameters tab.

One button to hide all worksheets that contain the string "TYPE"
One button to hide all worksheets that contain the string "AGENT"
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:

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.
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.
When button "Open by Type and Agent" is clicked, all these sheets should be made visible.Mark

xluser2007
06-09-2009, 08:43 PM
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:
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.
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.
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
06-09-2009, 08:56 PM
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.


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


Mark

xluser2007
06-09-2009, 09:05 PM
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
06-09-2009, 09:19 PM
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
06-09-2009, 09:21 PM
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
06-09-2009, 09:30 PM
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
06-09-2009, 09:35 PM
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 :thumb.

Thread marked as Solved.

regards

GTO
06-09-2009, 09:47 PM
You are most welcome; I am certainly happy to help. :friends:

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...


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


Just thought to mention in case handy :)


Have a great afternoon,

Mark

xluser2007
06-09-2009, 09:51 PM
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

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

GTO
06-09-2009, 10:04 PM
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
06-09-2009, 11:40 PM
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 :beerchug:.

xluser2007
06-10-2009, 06:24 PM
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
06-10-2009, 08:17 PM
I tried the following:

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

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
06-10-2009, 08:19 PM
I tried the following:

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
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
06-10-2009, 08:21 PM
Sorry VBAX MODS,

I accidentally double-posted, could you please delete the first of the duplicate posts.

thanks regards,

GTO
06-10-2009, 08:40 PM
I tried the following:

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

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?

First, please advise what version of Excel are you using?

I am currently in 2003, and do not see a MouseOver event listed. Did you get this from the drop-down, or manually type it in?

Okay now Me.Caption is not going to work at all. See, the Me keyword is referring to the worksheet who's module it is being used in. When Me is used in a UserForm's module, it refers to that userform.

Now since there's no .Caption property for a worksheet, we jam right there...

As to "hover text", please read the help topic for ControlTipText. Unfortunately though, this property appears unavailable for a commandbutton on a sheet.*

Mark

*-If I have that part wrong, and/or if there's a decently sensible workaround, could anyone please correct and/or explain? Pretty please?

xluser2007
06-10-2009, 08:52 PM
First, please advise what version of Excel are you using?

I am currently in 2003, and do not see a MouseOver event listed. Did you get this from the drop-down, or manually type it in?


Hi Mark, I am using 2003 (my name is a bit confusing huh).

To be honest I found the Mouseover code on a website a short while back, and can't seem to locate it for reference. I guess I was just getting depserate and trying anything.

And no, I can't see a Mouseover in 2003 either, probably should have checked first.



Okay now Me.Caption is not going to work at all. See, the Me keyword is referring to the worksheet who's module it is being used in. When Me is used in a UserForm's module, it refers to that userform.

Now since there's no .Caption property for a worksheet, we jam right there...

As to "hover text", please read the help topic for ControlTipText. Unfortunately though, this property appears unavailable for a commandbutton on a sheet.*

Mark

*-If I have that part wrong, and/or if there's a decently sensible workaround, could anyone please correct and/or explain? Pretty please?
Thanks for the explanation Mark.

I thought this feature might be obvious to put in.

but if an expert such as yourself feels there is no obvious solution, that is fine. The text on the commandbutton is self-explantory in itself. I was going for a bit of overkill :).

So if this is trublesome to put in, then no need for the hassle. But of there is a nice workaround, I am keen to learn.

Thanks and regards,

xluser2007
06-10-2009, 08:58 PM
Mark, just a thought, can you lock a command button in a particular cell.

i.e. if we always fix the command button to cell A1 (so it always moves and resizes with cells A1), then we can simply add a comment to cell A1 with the relevant caption, which acts like a hover text.

the reson for confining it to a cell is so that if we move the button, then we would need to move the comment loation.

So, for VBA curiosity more than anything else, is there a way to fix a commandbutton to a cell?