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)
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..........................
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
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 RelatedIn 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.
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 RelatedI 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?
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?
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 ...
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
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
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?
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?
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 RelatedOne of my work colleagues need to add a worksheet to an existing Excel Spreadsheet which has VBA behind it, but the worksheet is password protected. The developer who wrote the application has now left, so we have no idea what the password is. Is there anyway of getting round it, like cracking into the spreadsheet to find out the password, or another way?
View 2 Replies View RelatedI have this workbook to keep track of current and new work for the team - each individual inputs information on their own tab.
In turn, the current information is displayed on a summary page so we can all see who's doing what.
I want to protect that summary page and its formulas from accidental amendment.
However, while the summary page (when unprotected) updates as soon as someone enters new information on their own tab, the summary page won't update when I have it protected.
Is there any way of doing this - preferably without VBA as it's a work situation and the employer doesn't like VBA code running?
I want to run a macro in a worksheet which is proteced.
It's a simple macro which erases the content in (some) cells, nothing more. Even those celles are formatted as unlocked the macro fails.
I have the following code which works fine when Sheet1 is unprotected (The code is placed in Sheet3)
however if I protect sheet1 then the code does not work
I have tried it with
Code:
ActiveSheet.Unprotect
Code:
ActiveSheet.Protect
it still will not work on Sheet1
here is the code
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
On Error GoTo Handler
With Sheet1.Range("B6:B10045")
[Code] ....
I have a protected worksheet where I allow all users of the worksheet to filter yet when I filter, Excel gives a run time error 1004 - you cannot use this command in a protected worksheet. Could someone let me know what am I doing wrong?
View 9 Replies View RelatedI've built a workbook using Excel 2000 that uses several combo boxes. When I try to protect the workbook the combo boxes become locked, even when I have done FORMAT CONTROL / PROTECTION and unticked the LOCKED box. Is this normal?. I also have data validation cells and they work fine if I unlock them and Protect the workbook. As a result, I am thinking of converting the combo boxes to data validation cells, but should I even need to do this
View 3 Replies View RelatedI have the following macro and when it "reprotects" my worksheet I need it to also turn on these Protect Sheet properties:
Format cells
Format columns
Format rows
Insert hyperlinks
and I need it to leave on the defaults of Select locked cells and select unlocked cells
Sub Spell_Check()
ActiveSheet.Unprotect Password:="pmo"
Cells.CheckSpelling "SRdictionary.dic", SpellLang:=1033
ActiveSheet.Protect Password:="pmo", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub
I have a data list which needs to be updated by others on a shared drive. I want to protect the worksheet as there is other info on it (advancefilter from the main list). The problem is that the >>Data>>Form tool will open, but all the boxes necessary to update the list are greyed out (New, Delete etc.)
My other option is to create a macro to advance filter out the other data to another worksheet.
What if you forgot the password in a protected sheet? is there a way to unlock it?
View 1 Replies View RelatedI have created a table where the first 6 columns are data entry and the next 6 columns are formulas to give desired results.
I have made all the data entry cells unlocked and the rest of the spreadsheet locked (cells with formulas etc.)
I would like to be able to insert rows to the protect sheet which I have managed to achieve but when I insert the row the formulas do not update in the row.
I was able to create a macro that allows a user to unhide rows in a protected worksheet without unprotecting the other locked fields (see below). My question is: I want to add to this command so that 1 row can not be "unhidden" if the previous row is still hidden. For example, row 25 can not be unhidden if row 24 is still hidden.
Macro command used:
Private Sub Commandbutton4_Click()
Sub Hide_Rows2h()
ActiveSheet.Unprotect Password:="xxx"
Rows("25").Hidden = Not Rows("25").Hidden
ActiveSheet.Protect Password:="xxx"
I have A TABLE (Named Table1) and some columns filled by formula. (Like E,F,G,H columns) When I protect the worksheet E,F,G,H columns are locked, but A,B,C,D columns are UNLOCKED. When is sheet PROTECTED , If I add a row bottom of Table1, does NOT automatically fill the E,F,G,H columns and Table1 range does not expanding. But, if UNPROTECTED, it's OK. when add a row bottom of Table1, automatically fill all columns with formula and table range is expanding aotomatically with new row.
When sheet is PROTECTED , How can I allow, to add a row and aouto fill all columns like unprotected.
And I want to protect sheet in any case, after allowing to add row.
I need a macro code to do this.
I have (several) worksheets that have protection enabled. I have unlocked all the cells that users need access to and locked all the column and row headers/labels.
When I enabled protection on the sheets, the ability to add comments was taken away.
Is there a way to add comments to an UNLOCKED cell in a PROTECTED worksheet?
I was hoping to force end users to use a user form to input data into a work sheet. The problem is that the form wont input the data when the sheet is protected.
View 2 Replies View RelatedI have the following VBA in my spreadsheet.
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
ActiveSheet.Unprotect ("kellyz")
If Intersect(Target, Columns("o:o")) Is Nothing Then Exit Sub
For Each C In Intersect(Target, Columns("O:O"))
[Code] ......
As soon as i type anything in the columns, my sheet is unprotected. I need the sheet protected so that the users can not change my formulas.
I have a protected worksheet that from time to time I need to insert a new row and copy a specific range of cells that are with formulae (protected) to the newly inserted row. For a better idea of what I want to achieve, a snapshot of the worksheet is attached.
******** language="JavaScript" ************************************************************************>Microsoft Excel - ACR-INFRA.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutI23K23M23F24H24I24J24K24L24M24N24F25H25I25J25K25L25M25N25H26I26J26K26L26M26N26D28E28F28G28H28I28J28K28L28M28N28D29I29=
ABCDEFGHIJKLMNO22CR NoCodeStatusEstimateContr's PriceDeltaAgreed ValueCommitment Impact Impact ImpactFunded from23 2,730,382.00 320,000.00 180,000.00 241380AIA180,000.00
180,000.00 2,910,382.00 (180,000.00)140,000.00 0.00 180,000.00 0.00 252482AII
0.00 2,910,382.00 0.00 140,000.00 0.00 180,000.00 0.00 26 0.00 2,910,382.00 0.00 140,000.00 0.00 180,000.00 0.00 27 28 180,000.00 0.00 0.00 0.00 180,000.00 2,910,382.00 (180,000.00)140,000.00 0.00 180,000.00 0.00 29 A0.00 2,910,382.00 1036
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I would like to insert the new row at row27 downwards each time the macro runs.