RWayneH
asked on
Hidden sheet tabs, unhide them
I use the follow code on workbooks that have a ton of sheet tabs, it even show the hidden sheet tabs which is good. However when a hidden sheet is selected to goto, it fails. Is there a way to edit this so if a hidden tab is selected, it unhides it?
Private Sub cmdGoTo_Click()
If cboSheets.ListIndex <> -1 Then
Application.Goto Worksheets(cboSheets.Value).Range("A1")
Unload Me
End If
End Sub
Private Sub UserForm_Initialize()
Dim I As Long
For I = 1 To Worksheets.Count
cboSheets.AddItem Worksheets(I).Name
Next I
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What I'm assuming is that in your workbook that some sheets are hidden and that some are not, and after this process you want the hidden ones to remain hidden. If you were to use Rgonzo1971's code as is and a hidden sheet were selected from the combobox, that sheet would become visible until manually made hidden again. What my additional couple of lines of code does is to "remember" whether the sheet was visible or not, and if it's hidden, restore it to hidden after the process is done.
ASKER
Thanks for the explanation. What if the process is manual? Could it flip back to hidden, when a different sheet tab get the focus?
It only affects the sheet that is selected via the combobox, and the only thing it does is that after line 6 in the code in Post ID ID: 40306087 makes the sheet visible if it was hidden, line 8 would make it hidden again. (If it were visible to begin with it would stay visible.)
ASKER
Both EXCELlent solutions thanks.
Both EXCELlent solutions thanks.lol
You're welcome and I'm glad I was able to help.
In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
ASKER