Unhide multiple sheets that are NOT very hidden (at once)

Hi All,

I have found the web site of GR Business Process Solutions , who is offering a free add-in that allows the hiding and unhiding of multiple sheets (see http://www.grbps.com/ExcelB.pdf).
This is indeed a very nice and useful add-in, but it only has one snag: it is showing ALL the hidden sheets, including those who are VERY hidden.

I have tried creating some sort of loop that will identify the VeryHidden sheets and skip them (because those sheets are meant to remain really hidden from the users of the workbook) but failed.
Can someone help me out with such a modification to the code?

All the best, and thanks in advance

Re: Unhide multiple sheets that are NOT very hidden (at once)

Quote from jdawson422;776050

Hi,

Test the worksheet visible property and if it is equal to "xlSheeVeryHidden" then skip that sheet.

Thanks very much for your answer and sample code, but as I have said - this is exactly where I got stuck before (assuming I understood your answer ).
I have tried modifying the add-in code (the link was in my original post) and did not succeed in making it "skip" showing the VeryHidden sheets all together.
The add-in code shows a list of visible sheets and hidden sheets, and allows the user to hide the visible sheets or unhide the the hidden sheets.
The problem is that the code is also showing the VeryHidden sheets, on the same list as the "regular" hidden sheets.
This is what I am trying to avoid. VeryHidden sheets should remain VeryHidden and should appear in the form (list), at all.
I hope I have made myself (and my problem) clearer.

Again, thank you for your time and attention.

Re: Unhide multiple sheets that are NOT very hidden (at once)

Quote from jdawson422;776112

Hi,
Sorry for the misinterpretation of your post.
To make it work, I changed the Dim statement for arrSheets() from Boolean to Integer and several If statements to use xlSheetHidden, xlShetVisible, etc. and added a few extra If statements to make sure the very hidden sheets are not included.
......

Jim,
First of all, I would like to thank you very much following up on this. Very kind of you.
As I did not take that for granted - I also did some trials & errors and came up with the following code, which also seemed to be a good start, or so I thought...
This is where I was, prior to your last post, but was not sure how to incorporate this into the "logic" of the original add-in (if it is even possible)...:

Sub CheckIfVeryHidden()
    Dim SheetAsrray, i
    ReDim SheetAsrray(1 To Sheets.Count)
    For i = 1 To Sheets.Count
        If Sheets(i).Visible <> xlSheetVeryHidden Then 'this will show only the nonVeryHidden sheets
            SheetAsrray(i) = ThisWorkbook.Sheets(i).Name
            Debug.Print SheetAsrray(i)
            'Here I need to put the code that will do the additem to listbox, of all NON VeryHidden
        Else
            GoTo VeryHidden
        End If
VeryHidden:
    Next
End Sub

As to YOUR code, I tried it but ran into a few problems:

First, even though your code does the job in the initial part of the routine (the part the fires up the form from the module) -
it seems like there are other routines attached to the buttons in the form, which still shows the VeryHidden sheets once they are clicked.
Not sure how to tackle this.

Also, the snippet below does not allow the code to progress. It keeps firing up the message.
If I remove it - the form pops up, with the list of files

If ActiveWorkbook.Name = ThisWorkbook.Name Then
        MsgBox "That button not applicable for the active workbook."
        Exit Sub
    End If

Last thing (and probably connected to the problem described above), the code for the toolbar/button/commandbar does not work on my Excel (also 2010) for some reason (if it does something - I can't see it :? ).
No need to spend more time on this (at least not on my behalf), because I have custom ribbon tab with all my favorite macros, curtsy of "thespreadsheetguru", to which I will add the code.
http://www.thespreadsheetguru.com/blog/step-by-step-instructions-create-first-excel-ribbon-vba-addin

All the best
P.

Re: Unhide multiple sheets that are NOT very hidden (at once)

Quote from jdawson422;776121

Hi again,

Sorry, I forgot to fix one of the routines in the userform code module. The UpdateForm() routine should be:

As for the message about the active workbook, that was so that if I had it loaded as a non-addin (.xlsx instead of .xlam) I would be reminded that the wrong workbook was active. It is fine to remove that part.

Regards, Jim

Display More

Well, what can I say.... After a bit of a struggle, adding all your snippets and making some amendments to fix sum bugs - I am happy to say the code is finally finished.
Sooo..... Thank you very much, Jim. :congrats::thanx:
I am attaching the final file, for your benefit, and for anyone else who might need such an add-in.
BTW, for the purpose of testing, the file remains as xlsm. The code is fired from a button on sheet MAIN and there are 2 sheets already set to VeryHidden (sheets 1 & 2).

:cheers:

Re: Unhide multiple sheets that are NOT very hidden (at once)

Quote from cytop;776132

Thank you for the finished workbook.

If I may make a couple of suggestions, all 1 (well, nearly) line snippets:

.......

The first 2 procedures will move a sheet to the opposite list when double clicked (Saves user having to click a sheet name and then click a button to move a single sheet).

The last procedure will populate the Lists when the userform is first loaded.

Display More

As always, thanks for your insight and support. Will definitely give it a try.
Just so I am clear, these should go on the form's codes. Right? As-is additions to the existing codes? No change to the actual existing codes?

****UPDATE ****
No need to reply Jumped into the cold water and gave it try. Works like a charm ! Thank a lot for this improvement