Retain Use Of Group/Outline On Protected Sheets/Worksheets

Oct 5, 2006

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.

ws.Protect Password:="PASSWORD", userinterfaceonly:=True
ws.EnableOutlining = True

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

Sub 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

View 2 Replies


Group/Outline Worksheets When Protected

Aug 20, 2008

I Need the outline/group feature to work on a protected workbook with over 200 sheets, meaning a macro to cover the entire workbook not just a single sheet. This macro should travel with the workbook as it will be on many different peoples PC's

Private Sub Workbook_Open()
Dim cSheet As Integer
Dim tSheet As Worksheet
On Error Resume Next
cSheet = 1
Set tSheet = Worksheets(cSheet)
While Err.Number = 0
Call wbProtect(tSheet)
cSheet = cSheet + 1
Set tSheet = Worksheets(cSheet)
Err.Number = 0
End Sub..........................

View 2 Replies View Related

Group & Outline On Protected Worksheet

Jul 25, 2006

I curently using the group and outline to subtotal certain data. I do not want to data to be modified. As a result I protect the particular sheet. Problem is once I protect the worksheet, I can't expand the data hidden (using the group & outline). Is there anyway that i can expand the data & in the same time protect the
data. A sample in enclose(without worksheet protected)

View 6 Replies View Related

Group And Outline Columns

May 25, 2007

i have seen this cool feature in a workbook, but I am unable to find out how implement it myself. I can't really explain it (thus I am unable to find good search results) so I have added a little picture of what I mean. Its about adding controls for hiding and unhiding columns. How do I get such nice plusses and minusses into my workbook? I use Excel 2002

View 4 Replies View Related

Excel 2003 :: Group And Outline

May 14, 2013

In Excel 2003 I have Manually Grouped a number of rows. (Data>Group and Outline>Group).

If I use the buttons with the numbers 1, 2, 3 (indicating an outlining level) to collapse or expand the outline I receive the Error Message: "Cannot shift objects off sheet."

If I use the collapse button (-) or expand (+) button the data collapses and expands as I would expect.

I am aware of the normal issues surrounding objects and outlining and have ensured that I have changed the properties of all my of the objects in the sheet to "Move and size with cells". There are no comments in the grouped rows.

View 4 Replies View Related

Turn Off Group Box Outline And Label?

Sep 15, 2013

I know how to insert various groups of radio buttons on one sheet. They must be in their own group box control. The problem I have is that I don't see any option to turn off the resulting group box outline and label so it cannot be seen. Is there some way to do that in Excel 2010? I have 16 groups of two radio buttons across the top of the sheet and space is at a premium. If there was no outline it would look much nicer. The cells below the radio buttons function as the labels the way I am using them.

View 5 Replies View Related

Outline Macro Not Allowing Filtering On A Protected Sheet?

May 5, 2014

I have the following macro so that we can use the outline feature without having to unprotect a sheet.

Private Sub Workbook_Open()
With Worksheets("Schedule")
.Protect Password:="APQP", Userinterfaceonly:=True[code]....

However once the macro is run, filtering will not work without unprotecting the sheet first. If you choose not to run the macro, filtering will work without unprotecting the sheet however the outline feature will not. what I need to add and where to make both the outline and filter features to work without having to unptotect the sheet?

View 1 Replies View Related

Capture Group/Outline Row Hide/Show

Apr 26, 2008

The following is how the data is displayed:

[JAN] [a] [FEB] [b] [MAR] [c] [Q1] [APR] [d] [MAY] [e] [JUN] [f] [Q2] etc.

Basically, it is months and quarter. However I have an additional column that is hidden after each month - [a], [b], [c] etc. Those are displayed whenever a cell in the previous column is clicked. Now, I select all the columns from [JAN] to [c] and group them and I have a + on the top most row.

Problem is when I click on +, it expands the month as well as [a] [b] [c]. I have a macro to hide these, but how do I trap the [+] [-] action and run the hiding macro after that?

View 5 Replies View Related

Outline And Group And Subtotal Excel Data Automatically

May 19, 2014

Is there any way to outline excel data automatically

In the attached excel sheet : test outline.xls

I need automatically Because my data very large ...

View 2 Replies View Related

Outline And Rollup Subtotals Based On Outline Numbering In Column A

Jul 10, 2012

I have a spreadsheet that contains an outline-like number in column A, except where there are values in Column C. What I want is to automatically outline based on column A and subtotal at each succesive outline level (i.e. I would like to be able to roll up the subtotals to variousl depths of the outline).

1.1first level
1.1.1Second level level
value 5
1.2first level
1.2.1second level
value 7
value 4
1.2.2second level level
value 6

View 2 Replies View Related

Group / Ungroup Within Protected Sheet?

Jun 12, 2013

I have put the following code into my excel workbook to allow me to ungroup sections in a protected sheet. How can I change it to specify multiple worksheets?

Private Sub Workbook_Open()
With Worksheets("P5")
.EnableOutlining = True
.Protect Password:="rob", _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub

View 4 Replies View Related

Allow For Group/ungroup On Protected Sheet

Jun 1, 2006

I'm building a template for various users. I have protected the sheet with only certain cells available for input. The file is very large and there are some sections that can be group/ungrouped as the user desires.

however, once I protect the sheet, they can no longer click the - or + to expand and collapse. How can I enable group/ungroup on a protected sheet?

View 9 Replies View Related

Group/Ungroup In A Protected Shared Workbook

Aug 20, 2008

I used some code to allow grouping/ungrouping in a protected sheet. This works great! Enable Outline/Outlining on a Protected Excel Worksheet. However, when I share this protected workbook, all of a sudden grouping and ungrouping is not possible anymore. I get the same error as before, when the workbook was protected, not shared, and did not have the code mentioned above. Error: "You cannot use this command on a protected sheet. To unprotect..." Assumption: Is it possible that the Workbook Open Event is not being triggered if the workbook is a shared workbook?

Question: How can I group/ungroup columns in a shared (and protected) workbook?

View 6 Replies View Related

How Do You Allow Hide/unhide Or Group/ungroup Features While Protected

Dec 20, 2007

We have a file that needs to be updated monthly by running macros, but the macros are being blocked by protection. I would like to put some code at the start of the macro that unlocks all sheets and then at the end locks them up again. Actually, all I really need is a macro to do just that unlock all sheets, and then another one that locks them back up again as we will need to lock and unlock at various times and it is annoying to have to do it by sheet ....

View 14 Replies View Related

Delete Chart Series Across Sheets But Skip Protected Sheets

Mar 30, 2008

I have received following macro from someone to delete series but the problem is that it gives error when some sheets are protected, and I want those sheets to protected. When run it will ignore/leave protected sheets but delete series only from unprotected sheets

Private Sub CommandButton1_Click()
Dim Rng As Range, i As Long, r As Range, lVal, uVal
Dim DeleteCount As Double
Dim lRow As Long
Dim dr As Long
Dim dc As Long
dc = Sheets("Deleted Numbers").UsedRange.Columns.Count - 1
dr = Cells(Rows.Count, Sheets("Deleted Numbers").UsedRange.Columns.Count - 1).End(xlUp).Row + 1
If dr = 60001 Then ................

View 4 Replies View Related

Allow Sort, Sorting On Protected Worksheets

Aug 29, 2006

Is it possible to keep the sort icon available on a worksheet which is protected? I have issued a spreadsheet to colleagues which contains formulas so I have protected it, but I have now been informed that they need to be able to sort the data according to a ref number.

I thought of using code (which I'm not very good at) and used some from another excel document, but couldn't get it to work...the code was ....

View 9 Replies View Related

How Can I Group Worksheets On A Variable Key

Mar 31, 2009

I am using Excel 2007 with an extension from Adapx, Inc. of Seattle for electronic data capture and input.

I am trying to automate a process of inspecting and recording inspection data that has been collected and entered manually to this point. The data currently comes in as a variable quantity of 8.5 X 11 sheets. Some reports are 1 sheet; some are 7 or more sheets. In any case, the first sheet has all the ID information with some details and details of other samples are on subsequent sheets. When done manually, they are stapled together.

When done electronically, the report comes in as a series of worksheets. I describe the code I need as an electronic “staple” of a variable quantity of worksheets in Excel.

That is to say, I need to identify a group of worksheets as a single inspection report.

In order to make the plan work, I have the following idea. This is just my starting point. I am open to any ideas you care to share.

I need to have the current worksheet look at a specific cell in the previous work sheet.

If the value matches the value of the co-responding cell in the current work sheet, then copy the values of several specific cells into the current worksheet. This will repeat until the trigger value changes. It will increment by one. When it changes, I need to look at the next work sheet. If the trigger value matches the new trigger it will again copy the values from a specific set of cells of the current 'previous' worksheet into the then current work sheet. This sequence will repeat until there are no additional worksheets to process.

View 10 Replies View Related

Group On Multiple Worksheets

Dec 14, 2004

In an Excel Workbook with multiple worksheets set up exactly the same way, is there a way to select all sheets and perform "group and Outline" functions. I find that when I select all sheets, the "group and outline functons are not functional.

View 2 Replies View Related

Loop Through Worksheets, Some Protected, To Apply Macro

Jan 28, 2009

Cycle through all sheets in a workbook performing the following:Store worksheet protection state (bSheetProtection)... execute code ...Restore worksheet protectionI cannot seem to locate a way to save a worksheet's protection state in a variable.

View 5 Replies View Related

Group Selected Worksheets By Color

Jan 8, 2007

I would like to do is to sort only selected sheets. Can someone give me this additional code that can be incorporated in the code below. (If I just select the sheets I want sorted and run the code below, it sorts all worksheets irrespective of whether it is active or not).

Sub SortWorksheets()
Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean
SortDescending = False
If ActiveWindow.SelectedSheets.Count = 1 Then
FirstWSToSort = 1
LastWSToSort = Worksheets.Count
With ActiveWindow.SelectedSheets
For N = 2 To .Count ................

View 7 Replies View Related

Opening Protected Worksheets To Update Linked Data

Aug 3, 2006

I have the following code that should open all EXCEL workbooks in a
specified path, and unprotoect any password-protected worksheets to allow
for Link Updates, then close the workbook after password protecting it.

sub UpdateAllLinks()
Dim vLinkSources
Dim iLinkSource As Integer
Dim AnySheet As Worksheet
sPath = " C:Documents and SettingsShaneMy DocumentsHarcourt
sName = Dir(sPath & "*.xls")
do while sName <> ""...............

View 9 Replies View Related

Opening Password Protected/hidden Worksheets By The Manager

Oct 4, 2007

I've looked through this wonderful sight and haven't found one close to my project. I am a bit rusty on VB programming. I am working on workbook that contains multiple worksheets. Each worksheet is hidden and is accessible with a login and password so that only a specific user can access. My question is: How do I modify this so that the manager can open the workbook and view ALL worksheets without having to enter the login name and password for all of his users? Any help would be great. Thank you!!

Below is what I have so far: ....

View 9 Replies View Related

Expand/Collapse Columns On Password Protected Worksheets

Apr 30, 2003

Is there a way to password protect a document AND still allow for a user to expand or collapse columns?

Currently, it appear that when a worksheet is password protected, one cannot expand or collapse a column.

View 9 Replies View Related

How To Password Protected All Sheets At Once

May 15, 2014

i want to protect my all sheets at once

View 3 Replies View Related

Macros On Protected Sheets

Nov 21, 2006

I have a spreadsheet with some macros in it. When I run the macros, I would like to unprotect the worksheet and then protect the spreadsheets when the macros are done. The excel tabs I would like to protect/unprotect are called Graph and Data.

View 3 Replies View Related

Create Workbooks & Worksheets For Each Group In Table

Jan 17, 2008

I have a workbook that contains one worksheet with data. This data has 7 columns and is a database metadata report.

The columns are as follows:

Joined Tables
Column Name
Column Alias
Column Description
Column Data Type
Column Length

From this data, I am looking to have a script that automates the creation of a new workbook for each unique value in the Table column (i.e. tablename.xls) and saves them to my local drive. Each workbook then would have x number of worksheets named joined table 1, joined table 2, etc that relate to the name of the table in the original Table column. Finally, each worksheet will contain the related Column data for each Joined Table as mentioned above.

View 9 Replies View Related

How To Unprotect A Workbook And Sheets That Already Protected

Mar 10, 2014

I have a workbook that is protected by password. I have to unprotect sheets.

How can I unprotect sheets and workbooks while I am updating or changing?

View 13 Replies View Related

Search Column A In All Protected Sheets

Nov 15, 2008

I need a simple Macro that will, sheet by sheet, search the entire workbook. However, it only needs to search Column A, and there need be no text box, because the user will not define the text. The search text will simply be "X"

The real issue that I'm having is that I need it to find the first item, then offer me the option of selecting "Next" "Previous" or "Cancel" "Cancel" of course would mean that the item found is the one being searched for.

View 14 Replies View Related

Custom View When Other Sheets Protected,

Nov 29, 2007

I have a workbook with 3 spreadsheets. When I put ptotection on 2 of the sheets, the custom view will not work on the other. Any way to make the views work?

View 5 Replies View Related

Comboboxes, Autofilter And Protected Sheets

Jun 12, 2006

Everything works fine except when I password protect the sheet. Then when the autofilter is used an error message is triggered and the error relates to the first range selected in the combobox code. However, the combobox itself works fine on the protected sheet as I included code to unprotect the sheet at the beginning of the code run by the combobox and reprotect it at the end.
Any advice suggestions gratefully received.

View 9 Replies View Related

Copyrights 2005-15, All rights reserved