How to hide/show excel sheet 1

[baltog]

Hi all,

I have 1 workbook that contains 3 worksheets. On load i want to set the 2 worksheets hidden, then on set these visible on certain mouse or keyboard events.

Thanks in advance

 

[Grayfox081877]

try this

' Hide Worksheet
For Each sh In Sheets
sh.Visible = False
Next sh

'Gray

 

[Grayfox081877]

This will help you..


Sub HideSheet()
Worksheets("Sheet2&quot.Visible = False
Worksheets("Sheet3&quot.Visible = False
End Sub

Sub ShowSheet()
Worksheets("Sheet2&quot.Visible = True
Worksheets("Sheet3&quot.Visible = True
End Sub

For those who don't know how to place a shortcut key

1 Open Excel.
2 Right click on the Sheet name tab.
3 Select View Code from the Pop-up menu
4 Insert a New Module
5 Copy the code and paste it
6 Push Alt+Q to return to Excels normal view.
7 Push Alt+F8 and then select the macro name
8 Push Options and assign a shortcut key.



Gray

 

[PassingBy]

Can someone take this one step further and advise how I can put this so that it works On the Open Workbook event.

I keep getting run time error 9 subscript out of range

I have put the code in module 1, called it HideSheet and called if from the Open event but it doesn't work!

Very frustrated and any help appreciated.

Thanks
PassingBy

"Happiness is not getting what you want but wanting what you have."

 

[SkipVought]

Hi,

There are 3 states to the Visible worksheet property

xlSheetVisible
xlSheetHidden
xlSheetVeryHidden

The first 2 can be manipulated by the user from menu selection. The latter can ONLY be changed via VBA or in the VB Editor.

In this instance, Subscript out of range usually indicates a non-existant worksheet name. Make sure that your 2 macros are either in a MODULE or in the WorkBook Object.

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[PassingBy]

Skip

The following is located in Module 1

Sub HideSheet()
Worksheets("sheet1&quot.Visible = False
End Sub

I am trying to call HideSheet as follows

Private Sub Workbook_Open()
HideSheet
End Sub

Is this completely wrong?
I would like it to be xlSheetVeryHidden but its not essential.

Thanks
PassingBy


"Happiness is not getting what you want but wanting what you have."

 

[SkipVought]

and you DO have a worksheet named "sheet1"?

and Private Sub Workbook_Open() is in the WORKBOOK OBJECT and NOT in a module?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[RobBroekhuis]

Are you sure your worksheet is named "sheet1"? There is no other reason why your code wouldn't work. You can't hide the all worksheets, but the error message when you try to do that is a different one.


Rob

 

[PassingBy]

Guys

I confess I was referencing the wrong sheet name - my humble apologies, however, I have now corrected this to "Post codes", closed it all down and reopened.

Now I'm getting error 1004 unable to set the visible property of the worksheet class.

My hair is falling out rapidly!

PassingBy

"Happiness is not getting what you want but wanting what you have."

 

[SkipVought]

As Rob stated, you need to have at least 1 visible sheet in a workbook.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[PassingBy]

Skip/Rob
I have 2 sheets in the workbook
Post Codes
Input

Post codes is 100% protected
Input partially protected.

Could the protection be stopping the Call from working?
I will try unprotecting and see if the error occurs

PassingBy


"Happiness is not getting what you want but wanting what you have."

 

[PassingBy]

Thats cracked it!
The workbook was protected as well.

Remove the protection and the sheet became hidden, now all I need to do is re-protect once opened.

Should I only protect the remaining visible sheet or the whole workbook?

PassingBy


"Happiness is not getting what you want but wanting what you have."

 

[SkipVought]

Whatever your secutiry requirements dictate. Just remember the sequence of protecting and unprotecting.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[PassingBy]

Thanks for your help guys

The final solution was
Protect the sheets and leave the Workbook unprotected.

It seems that it cannot change the sheet properties on opening if the workbook is protected.

Star for your patience!

PassingBy

"Happiness is not getting what you want but wanting what you have."