If you type a password in the Password Optional text box, Excel opens the Confirm Password dialog box. In a specific area, however, i have merged cells of five columns in rows. It can generate charts, graphs, etc. Here, because the rows are next to each other, Excel cannot display separate plus buttons so users would have to use the numbered buttons instead. Chances are this post did not give you the exact answer you were looking for.
I am unaware of a way to directly enable the user to create new outline group while the sheet is protected, and so I hope that the approach above will help. I am using the code for enabling all worksheets for Grouping on a protected sheet that you offered in the original inquiry. Most of them, in order to run, have to un-protect the sheet and then re-protect it again. Mar 27, 2009 I have protected several sheets in my workbook to protect the forumlas and data for being tempered with by the users. Excel opens the Protect Sheet dialog box shown here in which you select the check box options you want to be available when the protection is turned on in the worksheet. Unprotect , the clipboard is empty! The solution I have in mind right now is just to indiscriminately run ungrouping vba line 10X and just place an error handler i. Name Case sSheet1 Case Else ws.
Without Dave's Macro the entire workbook becomes fully protected everytime you the workbook is saved. For later versions of Excel, this will promote the following dialog box: It is only when the sheet has been protected that locked cells are protected. Click on the box to remove the check mark so that group of cells will not be locked when you protect the sheet. Given below are the steps to protect your sheet. A password prevents other people from removing the worksheet protection—it needs to be entered to unprotect the sheet. I'd also love to see the userinterfaceonly property persist but, since it's existed in this form since.
Why are Halloween and Christmas the same? Thank you very much please see below for the code I used. So, the original vba code's syntax referred to the underlying sheet number. 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. It looks like they missed one though, I guess. If you would like to specify a password to unlock the sheet or workbook, you may do it here, but that is optional.
Sounds like something got missed there. One approach is to create a macro that the user can run, perhaps via a button on the worksheet, that will perform the grouping on the active rows. To leave certain cells unlocked so that you can still change them after protecting the worksheet or workbook, select all the cells as the cell selection, open the Format Cells dialog box Ctrl+1 , and then click the Locked check box on the Protection tab to remove its check mark. Because in these lines i have write some text and overcomes the rows, after, what i do is: i unmerge the rows and choose 3 rows from 5 columns together and merge. It is quite cumber some to change this for multiple tabs.
However, when I share this protected workbook, all of a sudden grouping and ungrouping is not possible anymore. A sample in enclose without worksheet protected Feb 3, 2012 I need to totally ungroup existing grouping of rows in a sheet. I also need some flexibility in that sometimes worksheets will be added or deleted, and I want the code to be flexible so that it always affects all worksheets without me having to name every worksheet in the code. This did not work - I am using Excel 2007, I went into Developer, Visual Basic, then selected my sheet - inserted this code and saved. Can you please tell me the permanent code. If you check it, this should allow sorting on the protected sheet. Jul 25, 2006 I curently using the group and outline to subtotal certain data.
Code: Sub GroupColumn Dim s As Worksheet For Each s In ActiveWorkbook. Selecting the Protect Sheet command makes it impossible to make further changes to the contents of any of the locked cells in that worksheet, except for those options that you specifically exempt in the Allow All Users of This Worksheet To list box. Do you have any suggestions? Please help on this issue Thanks Arun, Overall, it sounds like the solution is to ensure that the macro automatically runs each time the workbook opens up. Then, the first time you reopen the workbook after inserting the code, you should be able to expand the group controls even though the worksheet is protected. Open the Visual Basic Editor, drill into the Microsoft Excel Objects folder, and double-click ThisWorkbook. Say, when you turn on worksheet protection, you get a dialog box called Protect Sheet, where you are able to define an optional password.
I get the same error as before, when the workbook was protected, not shared, and did not have the code mentioned above. Having a central program to maintain your business records can make your life easier; however, if you have multiple users, you might need to maintain some security. So for example: If I have 6 columns A - F , when Date tab cell D4 reads 'Bud'. I've done all steps, but not sure how to do that one. Problem is that I do not know if the sheet has existing grouping, or if it does, how many levels of grouping.
Is there a way to include in the code these features so that they will remain? When you turn the protection off, the search function works just fine. Select unlocked cells Move the pointer to cells for which the Locked box is unchecked on the Protection tab of the Format Cells dialog box. 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. Sep 15, 2014 I have a protected sheet to avoid delete of functions. Worksheets 'Password Protect Current Sheet sht.
Is there any loss of data entered by other users? If you assign a password when protecting the worksheet or workbook, you must then reproduce the password exactly as you assigned it including any case differences in the Password text box of the Unprotect Sheet or Unprotect Workbook dialog box. I then added the enabling for the other functions I needed and it is all working just fine. The issue I have is that I want it to apply to all sheets, not just the active sheet. I hope you're doing well. The problem is not on any of the sheets in the other workbooks. Say you own the team status report worksheet, where you want team members to add data in specific cells only and not be able to modify anything else. Back to the drawing board.