Creating Custom Report Worksheets - Automated Data Analysis Using Excel - page 225

can be prevented from seeing data that others should not be allowed to see. Rows and Columns
can be hidden at will by setting the property to True. The subroutine

Hidden HideColRange

illustrates how to hide a selected range of columns on the current active Worksheet.

Sub HideColRange(colrng$)
Columns(colrng$).Select
Selection.EntireColumn.Hidden = True
End Sub

The reader should be aware that Charts and Worksheets can have their property

Visible

set to a special value termed property hides the object

so that the only way to make it visible again is by resetting this property in VBA code to

. The

xlVeryHidden xlVeryHidden

). The user viewing the object is not
capable of making the object visible. Such functionality can be useful to keep various users from
viewing portions of information contained within a Workbook. Some sample code follows for
invoking these properties using Excel VBA.

(or more specifically,

xlVisible xlSheetVisible

Sub HideWorksheet()
Worksheets(1).Visible = Hide 'Use Hide or False
End Sub

Sub ReallyHideWorksheet()
Worksheets(1).Visible = xlVeryHidden
End Sub

Sub UnHideWorksheet()
Worksheets(1).Visible = True
End Sub

Sub Toggle_Hidden_Visible()
'Use this to toggle between hidden and visible
Worksheets(1).Visible = Not Worksheets(1).Visible
End Sub

Sub UnhideAllWorksheet()
Dim sh As Worksheet
For Each sh In Worksheets
sh.Visible = True
Next
End Sub

Sub ReallyHideAllSheets()
On Error Resume Next
Dim sh As Worksheet
For Each sh In Worksheets
sh.Visible = xlVeryHidden
Next
End Sub

For the sample in this text, a number of things must happen to transition from Figure 6.3 to
Figure 6.4.