Tuesday, August 12, 2008

Permanently hide worksheet from user using VBA

Hide / Unhide Worksheets using Excel VBA (Excel 2007)

Here is the simple code to Hide and unhide Worksheet:

Sub Hide_Unhide_Worksheets()

Dim oWS As Worksheet

On Error GoTo Err_Filter

oWS = Worksheets(1)

' hide the sheet - user cannot unhide

oWS.Visible = xlSheetVeryHidden

' To make the sheet visible

oWS.Visible = xlSheetVisible

Finally:

If Not oWS Is Nothing Then oWS = Nothing

Err_Filter:

If Err <> 0 Then

MsgBox(Err.Description)

Err.Clear()

GoTo Finally

End If

End Sub

Here are the values for XLSheetVisibility. Hides the Worksheet so that the only way for you to make it visible again is by setting this property to True (the user cannot make the Worksheet visible).

Name

Description

xlSheetHidden

Hides the worksheet which the user can unhide via menu.

xlSheetVeryHidden

Hides the object so that the only way for you to make it visible again is by setting this property to True (the user cannot make the object visible).

xlSheetVisible

Displays the sheet.

No comments:

Post a Comment

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group