PDA

View Full Version : Solved: Unhide and hide desired sheets from macro



Barryj
03-18-2007, 06:37 PM
Hi, I have about 35 sheets that are hidden in a workbook, these sheets contain information that needs to be updated manually every now and then.

Is it possible to have a macro with either a drop down list or userform to call the required sheet and then hide the sheet again when it is closed.

johnske
03-18-2007, 08:57 PM
try here (http://xlvba.3.forumer.com/index.php?act=ST&f=6&t=280&st=0#entry386)

Barryj
03-18-2007, 09:33 PM
Thanks I will have a look and see how ig goes.

Barryj
03-19-2007, 01:57 AM
Jonhske, I forgot to mention that I have about 27 sheets that I dont require hidden, I only want to call the hidden sheets as required , sorry for the oversite!

johnske
03-19-2007, 02:39 AM
To unhide the sheets (put in standard module)...

Option Explicit

Sub UnhideSheets()
Application.Dialogs(xlDialogWorkbookUnhide).Show
End Sub


Put this code in the worksheet code modules that you want it to apply to, i.e. the hidden sheets. When unhidden, the sheet will be hidden as soon as you select another sheet.

Option Explicit

Private Sub Worksheet_Deactivate()
Me.Visible = xlSheetVeryHidden
End Sub

Barryj
03-19-2007, 03:12 AM
Is there a way of selecting the sheet that I want to unhide from a list or userform,I dont need all the sheets unhidden at one time only the one that needs updating.

johnske
03-19-2007, 03:24 AM
:dunno Thats exactly what xlDialogWorkbookUnhide does - didn't you try the last bit of code I gave?

Barryj
03-19-2007, 07:08 AM
I put the code in the slected sheets and it hides them when another sheet is activated but it wont allow me to unhide the sheets, in the format section when I go to unhide I am unable to.

Any Thoughts?

lucas
03-19-2007, 07:52 AM
Hi Barry,
You put the first piece of John's code in the thisworkbook module and it was supposed to be in a standard module and called by a button or something.....see attached.

Barryj
03-19-2007, 06:27 PM
Thanks Lucas & Johnske, works great, thanks again.

mdmackillop
03-20-2007, 01:36 AM
Have a look at this post (http://www.vbaexpress.com/forum/showthread.php?t=11954) for an alternative method