?
Solved

Excel protection, need to enable hide/unhide for users while protected

Posted on 2010-01-08
2
Medium Priority
?
412 Views
Last Modified: 2013-11-25
I have a routine which protects all cells in the workbook which contain formulas. I need to add to this routine an instruction to enable the user to hide/unhide. At the moment, if protection is enabled, the user cannot hide/unhide columns and rows.
Thank you!
JohnD
Sub prot()
Dim rng As Range
Dim prot As Range
Dim ws As Worksheet
 
    For Each ws In ThisWorkbook.Worksheets
        If ws.ProtectContents = True Then ws.Unprotect
        ws.Cells.Locked = False
        On Error Resume Next
        Set prot = ws.Cells.SpecialCells(xlCellTypeFormulas, 23)
        On Error GoTo 0
        If Not prot Is Nothing Then
            prot.Locked = True
            For Each rng In prot
                If rng.Parent.ProtectContents = False Then rng.Parent.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False
            Next
        End If
        Set prot = Nothing
    Next
 
End Sub

Open in new window

0
Comment
Question by:John Darby
2 Comments
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 2000 total points
ID: 26214074
I assumed you wan to give user the option of hide/unhide the rows even if the sheet is protected, If the answer is yes then use the following code.
Saurabh...

Sub prot() 
Dim rng As Range 
Dim prot As Range 
Dim ws As Worksheet 
  
    For Each ws In ThisWorkbook.Worksheets 
        If ws.ProtectContents = True Then ws.Unprotect 
        ws.Cells.Locked = False 
        On Error Resume Next 
        Set prot = ws.Cells.SpecialCells(xlCellTypeFormulas, 23) 
        On Error GoTo 0 
        If Not prot Is Nothing Then 
            prot.Locked = True 
            For Each rng In prot 
                If rng.Parent.ProtectContents = False Then rng.Parent.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False, AllowFormattingColumns:=True,AllowFormattingRows:=True
            Next 
        End If 
        Set prot = Nothing 
    Next 
  
End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:John Darby
ID: 31674779
Thank you for the below statements!

AllowFormattingColumns:=True,AllowFormattingRows:=True
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question