Dec 15, 2011 I am having a problem with one of my workbooks. Continue selecting and grouping inner rows until you have created all of the levels that you want in the outline. To display the data, drag across the visible column letters adjacent to the hidden columns. However, once I run any of the macros that un-protect and re-protect the sheet, I remain able to select and edit unlocked cells practically, for my purposes, this means that I can input data which will appear in the default font size and color of the sheet but I cannot format cells which, practically, for my purposes would allow me to occasionally change the font color and size of the data. However, when I share this protected workbook, all of a sudden grouping and ungrouping is not possible anymore. On the long run, I think the problem will be that if I want to modify this file and keep the solution, I need to unprotect this sheet to make it work on the next opening.
You may have to before you can post: click the register link above to proceed. The original macro code used the underlying sheet number, which can only be seen by opening the visual basic editor. Nov 10, 2009 I have a question relating to the use of autofilter in a protected sheet. Ok I figured out what you were getting at. Is there a way to include in the code these features so that they will remain? On the base of the sheets you will see an Object called ThisWorkbook.
Or any way to address this issue via some other method like adding a scrollbar,etc. For more information about using the Subtotal function, see. Click on the Like button under the post! Dec 20, 2007 I've locked certain columns on my worksheet so that users cannot overtype target dates etc. Many Thanks Debra, No i didn't protect the sheet programatically, i only locked the sheet by using the protect sheet under the tools menu. I get the same error as before, when the workbook was protected, not shared, and did not have the code mentioned above. Question: Is there a way to proctect the sheet and keep the Group and ungroup + sign function normally. So, in effect, the user can delete rows where there are no locked cells.
If you ungroup an outline while the detail data is hidden, the detail columns may remain hidden. I started with a fresh excel file in case there was something wonky in the one I was working on, and still it won't work. How can we insert new rows in a protected sheet? Sep 15, 2014 I have a protected sheet to avoid delete of functions. I hope you're doing well. Thanks Jeff Hello Jeff, I am new to this. When you turn the protection off, the search function works just fine. It is quite cumber some to change this for multiple tabs.
In that dialog box, you can check boxes that control what a user is allowed to do even though the sheet is protected. Say, when you turn on worksheet protection, you get a dialog box called Protect Sheet, where you are able to define an optional password. Best regards, René Rene, Hi there! Thank you very much please see below for the code I used. Use an outline to quickly display summary rows or columns, or to reveal the detail data for each group. I've tested the above change in your file, and it works like a champ.
Good luck, and ping me back if you get stuck. Also, if there is more than one sheet in the workbook, would it look as follows? Thanks, Jeff Hi jeff - in step 9 you say make sure macros are enabled. I do not want to data to be modified. Those that understand Binary and those that dont. Worksheets 'Password Protect Current Sheet sht. Depending on what his cell reads, I want to group and ungroup their respective columns. I can guarantee 9 times out of 10, one of my strategies will get you the answer s you are needing faster than it will take me to get back to you with a possible solution.
My code is working fine in setting the autofilter and locking down the sheet but a user then loses the ability to use the Data - Filter - Show All menu button. Mar 27, 2009 I have protected several sheets in my workbook to protect the forumlas and data for being tempered with by the users. Once i have closed the excel then again reopen the same excel file and tried with '+' or '-'. Is there any loss of data entered by other users? Is there a way to have the worksheet protected, allow the use of the auto filters, and allow the use of the show all button? The outline symbols appear beside the group on the screen. Without Dave's Macro the entire workbook becomes fully protected everytime you the workbook is saved. I guess you can write another macro to automatically unprotect when closing : I hope it helped. To group all of the detail data for row 11, select rows 2 through 10.
Detail data at lower levels is then hidden. Name Case sSheet1 Case Else ws. Feb 21, 2014 What I need to do is group and collapse certain columns when Cell D4 in my Date tab reads Bud. As a result I protect the particular sheet. Apr 26, 2007 What code would I use to test if the sheet is protected or not? To select the next and previous worksheets Ctrl+ Page Down or Ctrl + Page Up 2.