Select multiple sheet to unhide (Excel 2003)

Topic

New Reply

Is there a way to select multiple hidden sheets to unhide?

Reply | Quote

Replies

No, you have to unhide them one by one.

Reply | Quote

No, you have to unhide them one by one.

Or, if you want to unhide them all. you can run some code
[codebox]Sub unhide()
Dim a As Integer
On Error GoTo errSub
For a = 1 To 999
Worksheets.Item(a).Visible = True
Next a
errSub:
End Sub
[/codebox]

Reply | Quote

Or, if you want to unhide them all. you can run some code
[codebox]Sub unhide()
Dim a As Integer
On Error GoTo errSub
For a = 1 To 999
Worksheets.Item(a).Visible = True
Next a
errSub:
End Sub
[/codebox]

Thanks but I want to unhide some selected sheets and not all. The code will unhide all hidden sheets in workbook, I believe.

Reply | Quote

Yes, Gfamily’s code will unhide all sheets.

The Excel interface does not offer a method for unhiding multiple specific sheets in one go.

Reply | Quote

The Excel interface does not offer a method for unhiding multiple specific sheets in one go.

I dont understand any reason for this limitation. It is something irretating to repeat a particular task.

Reply | Quote

I dont understand any reason for this limitation. It is something irretating to repeat a particular task.

You can make the unhide an easier task by making your own custom toolbar and using Ctrl+drag to copy the the ‘Unhide…’ command from within the Format | Sheet drop down to make it available at the click of a mouse (keep the Crtl key depressed when dragging, otherwise you’ll move the command rather than copying it ).

I have both the Hide and Unhide commands exposed like this, it makes life a little easier at little cost on the workarea.

Alternatively, if this is a spreadsheet for which you will regularly want to unhide the same set of sheets, you could modify the macro to specify each required sheet in turn. I’m not sure whether you can do that by name, or whether you need to know the index number for each one. Not ideal I know, but it can be worthwhile to set the macro up once

Reply | Quote

I dont understand any reason for this limitation. It is something irretating to repeat a particular task.

You may be interested in the Navigator add-in I have been using it successfully for years now.

Reply | Quote

You could send a request to Microsoft to include it in the version after Office 2010…

Reply | Quote

You could send a request to Microsoft to include it in the version after Office 2010…

ha ha ha….

jokes apart.

If it is not included in 2007 & in 2010 too, I guess, there must be some reason behind it.

Reply | Quote

The attached workbook contains a module and userform that let you specify the sheets to be unhidden. Click the button on the first sheet to see how it works.

If desired, the module and userform could be incorporated in an add-in, and you could assign a custom toolbar button to the macro.

Reply | Quote

Is there a way to select multiple hidden sheets to unhide?

If this is a recurring task, and you can explain the consistent logic to be applied; then I am certain that code could be developed to achieve the desired results. Alternatively, you could build a macro which:

[*]collects the names of all hidden sheets;
[*]displays them in a ListBox, (need to confirm that ListBoxes will allow the selection of nonadjacent entries); and
[*]Unhides the selected sheets.

Reply | Quote

Here’s a fairly rudimentary add-in that hooks into the built-in Hide and Unhide buttons to provide a form for multiple hiding/unhiding. It’s unprotected so feel free to improve it as required!

Reply | Quote

Here’s a fairly rudimentary add-in that hooks into the built-in Hide and Unhide buttons to provide a form for multiple hiding/unhiding. It’s unprotected so feel free to improve it as required!

Thanks rory.

It remains first sheet from the list intact. I mean if the first sheet in the list is hidden/visible, I am not able to unhide/hide it. Second, there is a option of ‘very’ hide & a check box “Include ‘very hidden’sheets”. What it is all about? I am not a code friendly user so unable to extract results from reading script.

Reply | Quote

In Excel’s user interface, you can hide and unhide a sheet. In VBA, this corresponds to setting the Visible property of the sheet to xlSheetHidden or to xlSheetVisible.

In VBA, there is a third option for the Visible property. If you set it to xlSheetVeryHidden, the sheet will be hidden, and it will *not* be listed in the Format | Sheet | Unhide list. Such a sheet can only be unhidden using VBA.

Rory’s form lets you choose whether you want to list “very hidden” sheets.

Reply | Quote

Thanks all for your interest & valuable support.

I think I should go with “the must have” Navigator Add-in as suggested by Don. It includes the best one-go utilities.

Thanks

Reply | Quote

Sorry – I corrected that in the code, but it appears it didn’t save the changes to the add-in. Try this version.
There are two levels of hiding a sheet – you can hide it via the user interface or you can ‘Very hide’ the sheet in code so that it does not appear in the Unhide list. I added functions to hide/unhide those sheets too as it makes my lfe easier.

Reply | Quote

Working fine.

Earlier the ‘very hidden’ option was not working, as stated by Rory, thus I was not able to record the activity.

Thanks Rory
Thanks Hans

Reply | Quote

I am not sure how relevant is this?

The tools provided by Don in this topic consisting some utility I was looking for some time ago and the thread was intentionaly locked by StuartR for some specific reason.

PS : Infact said topic was deleted from the forum.

Reply | Quote

Stuart sent you a private message about that.

Reply | Quote

Stuart sent you a private message about that.

Hans, Pls don’t misunderstand me. I abide by Lounge’s rules & regulations.

I just wanted to let you know about any possible harm due to this post.

Reply | Quote