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.
try here
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Thanks I will have a look and see how ig goes.
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!
To unhide the sheets (put in standard module)...
[VBA]
Option Explicit
Sub UnhideSheets()
Application.Dialogs(xlDialogWorkbookUnhide).Show
End Sub
[/VBA]
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.
[VBA]
Option Explicit
Private Sub Worksheet_Deactivate()
Me.Visible = xlSheetVeryHidden
End Sub
[/VBA]
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
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.
Thats exactly what xlDialogWorkbookUnhide does - didn't you try the last bit of code I gave?
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
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?
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.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Thanks Lucas & Johnske, works great, thanks again.
Have a look at this post for an alternative method
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
Reply With Quote
