vba hide all sheets

[bensonsearch]

Hi All,

I need to hide all sheets from being seen by the users. I have put in the workbook open event the following

Sheet1.Visible = xlSheetVeryHidden
 Sheet2.Visible = xlSheetVeryHidden
 Sheet3.Visible = xlSheetVeryHidden
 Sheet4.Visible = xlSheetVeryHidden
 Sheet5.Visible = xlSheetVeryHidden
Sheet6.Visible = xlSheetVeryHidden

however it stalls on the sheet6 one saying it failed. I comment it out and all good but that sheet is still there. any idea how to hide them all just showing the form in the centre of a blank background of excel?

 

[klarowe]

Not sure if it will fix your problem but have you tried using a loop to help simplify the code. If its only the 6 sheets then its not a big deal, but if you have a bunch I could see that getting long.

Option Explicit
Sub Button1_Click()
    Dim i As Long
    
    For i = 1 To 6
        Sheets(i).Visible = xlVeryHidden
    Next i
End Sub

One thing I noticed though is that it seems like there has to be at least one sheet open. When I did a quick test, I had the same error come up on sheet 3 (only did 3 sheets). Could you add just a blank sheet at the end to leave one visible?

 

[Biz]

Try variant

Where you specific sheets you want to see only and rest would be hidden once macro is run.

'Hide sheets does not sheetnames:- Test,Data
Sub HideSheets()
Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If InStr(1, ws.Name, "Test", vbTextCompare) = 0 _
           And InStr(1, ws.Name, "Data", vbTextCompare) = 0 Then
            ws.Visible = xlVeryHidden
        End If
    Next ws
End Sub

 

[GTO]

I need to hide all sheets from being seen by the users....however it stalls on the sheet6 one saying it failed. I comment it out and all good but that sheet is still there. any idea how to hide them all just showing the form in the centre of a blank background of excel?

Hi there,

You can not hide all sheets is a workbook, at least one must be visiblle.

You can use the .IsAddin Property to hide the workbook, but you would need to make sure that the userform's being dismissed results in the property being reset to false.

Mark

 

[BradA]

I know I'm like a year late to this discussion but am poting this so that the answer is here next time someone searches for it.

You can in fact make it so that not sheet tabs are displayed.

Use

ActiveWindow.DisplayWorkbookTabs = False

and of course set to True to make the tabs visible again.

 

[Michael M]

Brad
That dosn't hide the sheets as requested, it simply hides the tabs !
The user could then use CTRL + Page Up / Page Down to change between sheets

 

[BradA]

Brad
That dosn't hide the sheets as requested, it simply hides the tabs !
The user could then use CTRL + Page Up / Page Down to change between sheets

Oh...well, then, never mind. Thanks for pointing that out. And here I thought I was contributing something worthwhile!

 

[Michael M]

That's OK.....we're all here for the learning process.
AND
At least you are contributing....we all have the occasional moment.....

 

[Dota2014]

I know I'm like a year late to this discussion but am poting this so that the answer is here next time someone searches for it.

You can in fact make it so that not sheet tabs are displayed.

Use

ActiveWindow.DisplayWorkbookTabs = False

and of course set to True to make the tabs visible again.

Thanks, this is just what I was looking for.

Brad
That dosn't hide the sheets as requested, it simply hides the tabs !
The user could then use CTRL + Page Up / Page Down to change between sheets

To solve this problem, you can simply hide all worksheets but one, so the user can't use CTRL + Page Up/Down.

I am working on making a home page for the user. So when the workbook opens, the user will only be shown the Home Page. Then there are some Macro buttons they use to do things. I was looking for a way to hide all worksheets to make the users think they have no control over anything but those buttons on the Home Page.