Scribd
Upload
3 views1 page

Protect ActiveSheet with Formatting Options

The document discusses protecting worksheets in Excel VBA without requiring a password. It shows code to protect all worksheets in a workbook while allowing formatting of cells, columns, rows, sorting, filtering, and using pivot tables. It also provides another code example that protects a single worksheet while allowing formatting of rows, which is important for hiding rows. A third code sample protects a worksheet while allowing formatting of both columns and rows.

Uploaded by

klanfa
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
3 views1 page

Protect ActiveSheet with Formatting Options

The document discusses protecting worksheets in Excel VBA without requiring a password. It shows code to protect all worksheets in a workbook while allowing formatting of cells, columns, rows, sorting, filtering, and using pivot tables. It also provides another code example that protects a single worksheet while allowing formatting of rows, which is important for hiding rows. A third code sample protects a worksheet while allowing formatting of both columns and rows.

Uploaded by

klanfa
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Sub ProtectSheetsNoPassword()

Dim wb As Workbook
Dim ws As Worksheet
Set wb = ActiveWorkbook
For Each ws In [Link]
[Link] DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
AllowFormattingCells:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowInsertingColumns:=True, _
AllowInsertingRows:=True, _
AllowInsertingHyperlinks:=True, _
AllowDeletingColumns:=True, _
AllowDeletingRows:=True, _
AllowSorting:=True, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True
'[Link] 'Uncomment if you want to unprotect the sheets
Next
End Sub
The AllowFormattingRows:=True argument is the one you need to focus on for hiding
rows.

[Link] Password:="yourPassword", DrawingObjects:=True, Contents:=True, Scenarios:=True _


, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True, UserInterfaceOnly:=True

Sub ProtectButAllowFormatting()

'PURPOSE: Protect Worksheet But Allow User to Format & Hide Columns or Rows
'SOURCE: [Link]

Dim myPassword As String

'Input Password to Variable


myPassword = "ExcelGuru"

'Protect Worksheet (Allow Formatting Columns)


[Link] Password:=(myPassword), AllowFormattingColumns:=True

'Protect Worksheet (Allow Formatting Rows)


[Link] Password:=(myPassword), AllowFormattingRows:=True

'Protect Worksheet (Allow Formatting Columns & Rows)


[Link] _
Password:=(myPassword), _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True

End Sub

You might also like