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)
Wend
Err.Number = 0
End Sub..........................

View 2 Replies


ADVERTISEMENT

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 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).

Code:
A B C
1.1first level
1.1.1Second level
1.1.1.1third level
value 5
1.2first level
1.2.1second level
value 7
value 4
1.2.2second level
1.2.2.1third 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

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
Else
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
Assessmentspassword"
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

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:

Table
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

Create Individual Worksheets For Each Related Group Of Table Range

May 25, 2008

I have a huge worksheet containing Blackberries expenses of my company divided by multiple sections. Each section follows the following pattern: Example:

Client 222-5555 MY COMPANY NAME LCC
International Calls
- Call to Bahamas on June - $50,00
Local Calls
- Call to NY on June - $ 30,00
Total Price - 80,00

I would like to select each row between client and total price and paste then on multiple sheets in order to create individual invoices. The selection must be based on the client number (for instance 222-5555) that is part of the cell value.

View 4 Replies View Related

Outline/wbs Sort

Jun 25, 2007

have an Excel VBA function to sort an Outline/WBS?

I have tried the conventional Excel text and numeric sort but they do not work on an Outline/WBS.

I have a column in an excel workbook with a Outline/WBS.
If a user uses the default Excel sort I need to sort the Outline/WBS back to the correct Outline structure (example below):

1
1.1
1.2
1.3
1.3.1
1.3.2
2
2.1
2.2
2.2.1
2.2.2
2.3

View 9 Replies View Related

Selected Cell Outline

Oct 9, 2008

The outline that indicates my selected cell is a very pale blue which I can hardly see. how to change it to black or some color I can see?

View 3 Replies View Related

Outline.ShowLevels Event And Value

Jun 12, 2006

1. Is there an event when Outline.ShowLevels are changed? I want to hide some columns when the outline level changes depending on the level. For example if Outline.ShowLevels is 2 I want to hide certain columns and if Outline.ShowLevels is 3 I want to unhide those columns. I realise you can have a mix of levels showing but I will hideunhide the columns based on what has changed ie from level 2 to level 3 or vice versa.

View 6 Replies View Related

VBA To Search Group Of Cells And Input Message If Value Is Missing From Group?

Mar 17, 2014

I am trying to write VBA code that groups cells between blank rows and inserts a message if a string of text (a name) is missing from the entire group. I want the code to search cells in column A and group the cells between blank cells. Use the name I input in an input box as the search criteria and insert a message in the Column C next to the last blank cell in the group and then move on to the next group and highlight the cell in red with bold text. I am including a spreadsheet with an example of what the sheet should look like before and after the code is run.

View 11 Replies View Related

Pivot Table Group :: Group By Integers And Not By Months, Years Etc?

Nov 12, 2009

I have date fields as column labels in a pivot table. When I try to group them I'm only given the option to group by integers and not by months, years etc.

I've had a look at the format settings of the column and they are formatted as date fields.

View 9 Replies View Related

Enable Outline But Protect Sheet

Sep 15, 2007

I have a sheet that is several hundred rows long and so I have grouped like data so the user can expand or colapse the data they would like to see. There is nothing on the page the user will need to modify so I have protected the page so they cannot make changes. If I protect the sheet then I am no longer able to expand or colapse the groupings.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved