Hide/ Unhide - xlSheetVeryHidden

[NSN_VBA]

Hi,

I am looking to hide an array of worksheets as shown below;

Worksheets(Array("Data Log", "Rev. History", "Visual Set Point")).Visible = False

This works great to hide the worksheets, however what I am really looking to do is, very hide the worksheets, something like this;

Worksheets(Array("Data Log", "Rev. History", "Visual Set Point")).Visible = xlSheetVeryHidden

This doesn't work so great!

If anyone can let me know a better way for writing this code that'll be great

 

[Fluff]

How about

   Dim ws As Worksheet
   
   For Each ws In Sheets(Array("Data Log", "Rev. History", "Visual Set Point"))
      ws.Visible = 2
   Next ws

 

[NSN_VBA]

Mate you're an absolute legend! That works great.

In addition to this, I am also having troubles. If all worksheets are already hidden I get a;

Run-time error '1004':

Method 'Visible' of object'_Worksheet' failed

How do I check whether all worksheets are visible before running this code?

 

[Fluff]

You shouldn't get that error.
Is your workbook protected?

 

[NSN_VBA]

Hi, yes I protect it when I hide it, and unprotect it when I unhide it. I tried turning off the protection and it works great, but I will need the protection on.

Maybe an IF something?

Here's my complete code;

Option Explicit


Sub HideSheets()

Dim ws As Worksheet


For Each ws In Sheets(Array("Data Log", "Rev. History", "Visual Set Point"))
ws.Visible = xlSheetVeryHidden
Next ws


ActiveWorkbook.Protect Password:="Calculator", Structure:=True


End Sub


Sub UnhideSheets()


Dim ws As Worksheet


ActiveWorkbook.Protect Password:="Calculator", Structure:=False


For Each ws In Sheets(Array("Data Log", "Rev. History", "Visual Set Point"))
ws.Visible = xlSheetVisible
Next ws


wsDashboard.Unprotect "Calculator"
wsDataLog.Unprotect "Calculator"
wsVisualSetPoint.Unprotect "Calculator"

End Sub

 

[Fluff]

Unprotect the workbook first like

Sub HideSheets()
   Dim ws As Worksheet
   ActiveWorkbook.Unprotect "Calculator"
   For Each ws In Sheets(Array("Pcode", "MA", "Fluff"))
      ws.Visible = 2
   Next ws
   ActiveWorkbook.Protect Password:="Calculator", Structure:=True
End Sub

 

[NSN_VBA]

Great, that works perfect! Have a good weekend, cheers

 

[Fluff]

Glad to help & thanks for the feedback