Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Allow expand/collapse of Excel outlines on protected sheets

Our enhancement request is to allow the user to expand or collapse Excel
outlines (grouped rows or columns controlled by the +- icons in the
worksheet headings) when the worksheet is protected. This could be easily
provided as one of the many other options for 'editing exceptions' displayed
when protecting a worksheet.

This would be a huge visual usability asset to the end user. While the user
or designer often wants to lock the data or formatting, they do not
necessarily want to restrict the ability of the end user to show or hide the
same information. We have many templates that would benefit from the
flexibility of being able to quickly show or hide columns on a protected
sheet.

One simple example is when the user is using a template in Excel as a tool
to enter information into another program, such as an accounting program. In
this case, Excel may be docked in a half-screen window for reference to an
application docked in another half-screen window. In such cases, the user
could hide columns in the worksheet so that pertinent information would be
quickly available in the resized Excel window. Zooming often makes the text
too small to be readable, but expanding/collapsng outlines preserves the
readability while displaying the desired information.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc


Post your answer or comment

comments powered by Disqus
Hi. I have a dedicated button on my main data sheet which protects and unprotects it (password protected) when it is unprotected it only allows the user to input in a given range, so other codes are not deleted.

I am wondering if someone with more knowledge than me (not hard) can help with a little task. The following code works great but I would like the user to have 3 attempts at inputting the password before the workbook closes itself.

is it possible to display a message after each attempt, say "Wrong password, Please try again" or something?

Any help will be much appreciated

Thanks

code is as follows


	VB:
	
 
Private Sub CommandButton3_Click() 
  
     'Command button to protect & unprotect sheet.
  
    On Error Resume Next 
  
    Const PW As String = "2001" 'to change the PW
    If CommandButton3.Caption = "Protect Sheet" Then 
        ActiveSheet.Unprotect PW 
        ActiveSheet.Protection.AllowEditRanges(1).Delete 
        ActiveSheet.Protect PW 
        CommandButton3.Caption = "Unprotect Sheet" 
    Else: CommandButton3.Caption = "Unprotect Sheet" 
        ActiveSheet.Unprotect 
        CommandButton3.Caption = "Protect Sheet" 
        ActiveSheet.Protection.AllowEditRanges.Add Title:="Range1", Range:=Range("B4:L55") 
  
  
    End If 
  
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I currently have an auto_open sub which protects a worksheet upon opening and also enables outlining on this sheet. I would also like to enable the fill color function. How can I add this feature to the user interface? My code is as follows (the password has been changed for security).


	VB:
	
 
Sub auto_open() 
    With Worksheets("Main Budget") 
        .Protect Password:=*******, userinterfaceonly:=True 
        .EnableOutlining = True 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi

I have a sheet that I protect but i use the below code so that I can still use my gouping '+' or '-' symbols to hide/unhide rows.


	VB:
	
 
ws.EnableOutlining = True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This is ok but When close the workbook and reopen it, my sheet becomes fully protected and i cant use the group icons.

Can anyone either suggest better code or a way to initilise the workbook to act in this way when it loads up.

For Reference Below is my full code:


	VB:
	
 ProtectAll() 
     
    Dim ws As Worksheet 
    sSheet = Control.Name 
     
    For Each ws In ThisWorkbook.Worksheets 
        Select Case ws.Name 
        Case sSheet1 
        Case Else 
            ws.Protect Password:="PASSWORD", userinterfaceonly:=True 
            ws.EnableOutlining = True 
        End Select 
    Next ws 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks

James

The option to allow the inserting of rows in a protected sheet does not appear to work both in ticking the box when protecting the sheet and with vba.
Is this an error in design?
Is there a way to get this to work?

Thanks for any help Malcolm

My VBA code password protects all sheets every time I open this Spreadsheet.
But when I try to refresh the data, I get an error "Cannot Edit Pivottable on protected sheet"
How could I, using VBA,
1)unlock the sheets
2)refresh the Povottables
3)lock the sheets again

Thanks in advance

Hello!

I have a button on a sheet to add new rows using David McRitchie's InsertRowsAndFillFormulas code.

I want to restrict the range that gets used so I came up with this:

Private Sub cmbAddRow_Click()
If Intersect(Selection, Range("A12:B40")) Then
MsgBox "You are going to add rows below cell " & Selection.Address & "."
Call InsertRowsAndFillFormulas
Else
MsgBox "You can't insert rows(s) below cell " & Selection.Address & "."
End If
End Sub
If the cursor is in A12:B40, the code works fine. However, if the cursor is in column C (for example), and the button is pushed, I get Run-time error 91, Object Variable or With Block Variable not set.

Anyone see how to fix this code?

Lost

Hi

Can some clever sole tell me how I can allow comments to be inserted into a protected sheet?

Thanks
ExcelBob

Hello, is it possible use autosum on protect sheet? Standard is don´t visible this function on menu when sheet is protect.

Thanks George

Hi I have a pivot table and Pivot chart that are on one sheet. It was working fine but When I have protected my sheet That shows error

"Pivot table can not refreash on protected sheet..."

I have used following VBA code as follows

Private Sub Worksheet_Activate()
Me.Protect userinterfaceonly:=True
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Me.ScrollArea = "A1:S51"
Me.Range("A1:S1").Select
ActiveWindow.Zoom = True
Me.Range("A3").Select
ActiveSheet.PivotTables("EnergyReassPivotChart").PivotCache.Refresh
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
I have used Me.Protect userinterfaceonly:=True but still no success
http://www.mrexcel.com/forum/showthread.php?t=481591

I want to be able to automatically create a new sheet and in cell A1 of each sheet create a data web connection from a list of URL's on another sheet.

This is the code for creating a Data Web Connection.

    With
ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://slp.somerset.gov.uk/_layouts/spusagesite.aspx", Destination:= _
        Range("$A$1"))
        .Name = "spusagesite"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "46,49,50,53,54,56"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
I would need the spreadsheet to do the following:
Create a new sheet
Rename the sheet (based on data in a different sheet [lets call this Sheet A])
Select Cell A1
Create the Data Web Connection based on the URL in sheet A

& Repeat this process, going down the data stored in Sheet A.

I Hope this all makes sense, do let me know if anything is unclear.

I am looking to achieve the following 3 actions on a workbook (see attached) I am designing:

1) Sort on the data table on the sheets (Range A2:CM87). The range may change if new rows are added or deleted so if can be done dynamically, that would be ideal. I would love to accomplish the sorting by the following method I found from another site
Method that allows to sort easily by clicking on the header.

You could assign a macro to hidden rectangles in the header rows. The macro
would unprotect the sheet, sort by that column and reprotect the sheet.

Sample code and file found at Debra Dalgleish's site:
http://www.contextures.com/xlSort02.htmlI attempted to use this in my file and received errors. It seems pretty straightforward so I'm not sure why I am getting errors but I am a novice at VB. I would like to have all 91 columns to have the sorting capability.

2) Allow Filtering on the data table

3) Allow users to Expand/Collapse Groupings on the Sheets

I have some Workbook_Open code in the file which allows Outlining but doesn't allow Filtering, Sorting, etc. after the workbook is opened up.


	VB:
	
 Workbook_Open() 
    With Sheet1 
        .Protect Password:="", UserInterfaceOnly:=True 
        .EnableOutlining = True 
         
    End With 
    With Sheet2 
        .Protect Password:="", UserInterfaceOnly:=True 
        .EnableOutlining = True 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I attached the sample workbook that I am working with. It's password protected but I didn't use a specific password = "". Any help will be greatly appreciated. I looked for hours trying to find answers for all of these and I can't seem to get it all working.

Hi,

I have outline on three protected worksheets in a workbook, and I want other users to be able to collapse and expand the outline while the worksheets are protected. I entered the following code (which works fine):

Option Explicit
Sub auto_open()
With Worksheets("REPORT1")
.Protect Password:="test", userinterfaceonly:=True
.EnableOutlining = True
End With
End Sub

I then try the following (but it's not working):

Option Explicit
Sub auto_open()
With Worksheets("REPORT1")
With Worksheets("REPORT2")
With Worksheets("REPORT3")
.Protect Password:="test", userinterfaceonly:=True
.EnableOutlining = True
End With
End Sub

Any help, please?

Thank you,
Gos-C

Hi,

I've read a few of the other posts on allowing use of grouping in a protected worksheet but 'still' can't get it to work.

Here is my code. It switches between a 'partial' view of worksheet with some columns/rows grouped and a full view with all columns/rows ungrouped. I'm a beginner with VBA having learnt so far from this forum. Great ... awesome forum btw.


	VB:
	
 
Sub Partial_View() 
     
    Dim cap As String 
     
    Application.ScreenUpdating = False 
    Application.Calculation = xlCalculationManual 
    ActiveSheet.Unprotect 
     
    ActiveSheet.DrawingObjects(6).Select 
     
    cap = Selection.Characters.Text 
     
    If cap = "Full View" Then 
         
        cap = "Partial View" 
         
        ActiveSheet.Outline.ShowLevels RowLevels:=2, ColumnLevels:=2 
    Else 
         
        cap = "Full View" 
         
        ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 
         
    End If 
     
    Selection.Characters.Text = cap 
     
    Application.ScreenUpdating = True 
    Application.Calculation = xlCalculationAutomatic 
    ActiveSheet.EnableOutlining = True 
    ActiveSheet.Protect 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any ideas on how to make this work. I'm getting major frustration now.

Thanks,

Simon

Hi, I am using Dave Hawley's code to allow grouping/ungrouping on a protected sheet:

	VB:
	
 Worksheet_Activate() 
    With Me 
        .Protect Password:="eb", UserInterFaceOnly:=True 
        .EnableOutlining = True 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I need to add the option of inserting rows to the protected sheet using this code. I am using an Excel 2007 file. PLEASE HELP!!
Thank you,
Reayn.

When using MS Excel 2002: I have turned on "Protect Sheet" to protect some
formulas from being deleted. Prior to turning on protection I unlocked the
cells that I wanted users to be able to edit. And I have checked the box to
"Allow all users of this worksheet to:" "Delete Columns".

What I *don't* see on the "Protect Sheet" menu is an option to "Hide Columns"

Do you know of any way that I can allow users to do this, without turning
off the protection in MS Excel 2002?

When using MS Excel 2002: I have turned on "Protect Sheet" to protect some
formulas from being deleted. Prior to turning on protection I unlocked the
cells that I wanted users to be able to edit. And I have checked the box to
"Allow all users of this worksheet to:" "Select Locked Cells; Select Unlocked
Cells; Format cells; ".

What I *don't* see on the "Protect Sheet" menu is an option to "Allow all
users of this worksheet to: INSERT COMMENTS into unlocked cells.

Do you know of any way that I can allow users to do this, without turning
off the protection in MS Excel 2002?

Anyone smarter than me,
I have been asked to create an excel file that can keep track of a lot of information concerning training excercises for the employees of the company. As there are many different sections and many requirements inside those sections, I thought I would make it easier on the person that had to edit the sheet by grouping the requirements in each section together so you could show/hide sections and make the sheet smaller to elimate the need to scroll up and down the huge document. Anyway, I was able to do that fine, now the problem I am facing is that now, I need to protect the sheet and workbook so that no one accidentally changes any of my formulas or requirements. However, when I protect the sheet, all users are now unable to show/hide details of the groups. I have tried various different permissions and things and haven't had any luck. Anyone have any good ideas or references that I can look at?
Thanks,
Danaki

When using MS Excel 2002: I have turned on "Protect Sheet" to protect some
formulas from being deleted. Prior to turning on protection I unlocked the
cells that I wanted users to be able to edit. And I have checked the box to
"Allow all users of this worksheet to:" "Delete Columns".

What I *don't* see on the "Protect Sheet" menu is an option to "Hide Columns"

Do you know of any way that I can allow users to do this, without turning
off the protection in MS Excel 2002?

When using MS Excel 2002: I have turned on "Protect Sheet" to protect some
formulas from being deleted. Prior to turning on protection I unlocked the
cells that I wanted users to be able to edit. And I have checked the box to
"Allow all users of this worksheet to:" "Select Locked Cells; Select Unlocked
Cells; Format cells; ".

What I *don't* see on the "Protect Sheet" menu is an option to "Allow all
users of this worksheet to: INSERT COMMENTS into unlocked cells.

Do you know of any way that I can allow users to do this, without turning
off the protection in MS Excel 2002?

Hi all,

I am creating a worksheet to be used by a lot of different users and most of them have macros disabled as their default security status. I want to aply protection to the sheets so that they can enter data only where i want them to and want them to be able group and un group the columns. i have some code that will enable them to enable outlining but they need to enable macros first.

Is there a way of protecting my sheet and allowing users to group and un group the sheets as soon as they open the work sheet?

I'm familiar with the code to allow grouping/ungrouping in a protected spreadsheet; thanks to the this old thread, http://www.ozgrid.com/Excel/outlining-protected.htm.

How would I code to allow for hiding/unhiding cloumns and rows while still protecting the spreadsheet?

Also, how could i allow for this file to be shared? Currently i'm getting an debug error when another person enters the file.

When I have a sheet password protected on Excel 2003, I am still able to Freeze Panes

Same sheet, (or any for that matter) in Excel 2007 password protected, does not allow Freezes Panes. The choice is grayed out.

Is there a way around this? VBA soloution would be okay also.

Thanks
Harry

Hi,
I need help with two related problems:

1) What macro do I need to run in Excel 2000 or 2003 that will allow a protected sheet to run an A to Z sort?

2) Assuming my first question is possible, is it possible to sort data on sheet 1, which will then automatically sort related data on sheet 2 and 3? Or do I have to individually sort each sheet?
For example, if on sheet 1 I have names and gender of pupils, on the second sheet I have the pupils spelling test grades and on the third sheet is their maths grades. On both the 2nd and 3rd sheet the name and gender are taken from sheet 1, i.e. they automatically update when a new name/gender is entered or names are sorted. And there lies my problem, if I sort the names on sheet 1, they will automatically sort themselves on sheet 2 and 3, but the other data doesn't. Any ideas to solve this problem will be gratefully received.

With the worksheet protected (with a password that I don’t want to share with the user) and the cell locked, I need a macro that can be activated to unprotect the following Named cells on the following worksheets

Worksheet = Mo1
Named cells L1RateMo1, L2RateMo1, L3RateMo1

Worksheet = Mo2
Named cells L1RateMo2, L2RateMo2, L3RateMo2

I’m using MS XP and Excel 2003, but some users have older versions Excel.
Obviously I'm lost. Thank you for your help.


	VB:
	
 UnlockCells() 
     
     
     
    Application.ScreenUpdating = False 
     
    Sheets("Mo1").Select 
     
    Worksheets("Mo1").Range("L1RateMo1").Locked = False 
    Worksheets("Mo1").Range("L2RateMo1").Locked = False 
    Worksheets("Mo1").Range("L3RateMo1").Locked = False 
    Worksheets("Mo1").Protect 
     
    Sheets("Mo2").Select 
     
    Worksheets("Mo2").Range("L1RateMo2").Locked = False 
    Worksheets("Mo2").Range("L2RateMo2").Locked = False 
    Worksheets("Mo2").Range("L3RateMo2").Locked = False 
     
    Worksheets("Mo2").Protect 
     
    Application.ScreenUpdating = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines



No luck finding an answer? You could always try Google.