|
Friday, June 12, 2009 - 11:36 AM
Public Sub HideSheets(bHide As Boolean, Optional CalledByVBA)
'make sure hidden worksheets are hidden, or expose all hidden sheets if hide = false (this is just an example)
Dim ws
'hide or show hidden sheets
For Each ws In wsHidden
ws.Hidden = bHide
Next
'show UI sheets (in case user hid any)
For Each ws In wsUI
ws.Hidden = False
Next
End Sub
Public Sub SetProtection(Enable As Boolean, WSName$, Optional CalledByVBA)
'set worksheet protection for all sheets (this is just an example - could exclude hidden sheets)
If (WSName = "Workbook") Then
Dim ws
For Each ws In ThisWorkbook.Sheets
If Enable Then ws.Protect Else ws.Unprotect 'may need to use a different method for xl 02/03
Next
ElseIf SheetExists(WSName) Then
If Enable Then Sheets(WSName).Protect Else Sheets(WSName).Unprotect
Else
'error
End If
End Sub
Public Function CountVisibleSheets&(Optional WBName$)
'uses TestLen function (see Functions category if this site)
Dim ws: If Not TestLen(WBName) Then WBName = ActiveWorkbook.Name
For Each ws In Workbooks(WBName).Sheets
If ws.Visible Then CountVisibleSheets = CountVisibleSheets + 1
Next
End Function
Public Sub fFreezePanes(Optional RowNum& = 1, Optional ColNum& = 0)
'freeze panes, specifying where to do it instead of excel guessing
'window and sheet must be active
With ActiveWindow
.SplitColumn = ColNum: .SplitRow = RowNum: .FreezePanes = True
End With
End Sub
|
|
| << Navigate to Friday, June 12, 2009 |
Add New Comment |