If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Expand/Collapse Grouped Data in Protected Worksheet
I've created a worksheet that will be distributed to 300+ users in an
investment group. It contains formulas and computations in cells that could easily be overwritten by spreadsheet novices, so I've protected the worksheet so users can only edit the cells they need to for the sheet to return the info they need. The problem is: after protecting the worksheet, Excel won't allow the user to expand or collapse grouped rows that list detailed information on each step of the calculations. This is inconvenient because I'd like to give the users the ability to expand the spreadsheet to see what's going on, but collapse the extra data for printing purposes. MS Support said that this functionality wasn't available (but it should be), so I'm curious if anyone has ideas for a good workaround? The spreadsheet can be grabbed from www.realrobert.com/foreclosure/worksheet.xls - password is blank to unlock. Any feedback would be greatly appreciated! |
#2
|
|||
|
|||
Robert,
I modified your workbook slightly, added some code and a couple of buttons from the Forms toolbar. I believe it does what you want. 1. Insert a row just above "auction items", call it Details and format it the same as the Summary row. 2. Clear the outline 3. Add a button to cell B25 with the caption "Details" 4. Add a button to cell C25 with the caption "Summary" 5. Add a module to your workbook and insert the following code... '----------------------------------------------- Sub ShowTheDetails() ActiveSheet.Unprotect If Rows(27).Hidden = True Then Rows("27:43").Hidden = False Else Rows("27:43").Hidden = True End If ActiveSheet.Protect End Sub Sub ShowTheSummary() ActiveSheet.Unprotect If Rows(45).Hidden = True Then Rows("45:53").Hidden = False Else Rows("45:53").Hidden = True End If ActiveSheet.Protect End Sub '---------------------------------------------- 6. Assign ShowTheDetails macro to the "Details" button. 7. Assign ShowTheSummary macro to the "Summary button. I will be glad to send you the workbook with the changes in it, if you would like to see it. Remove XXX from my email address. Regards, Jim Cone San Francisco, USA XX "Robert Slattery" wrote in message ... I've created a worksheet that will be distributed to 300+ users in an investment group. It contains formulas and computations in cells that could easily be overwritten by spreadsheet novices, so I've protected the worksheet so users can only edit the cells they need to for the sheet to return the info they need. The problem is: after protecting the worksheet, Excel won't allow the user to expand or collapse grouped rows that list detailed information on each step of the calculations. This is inconvenient because I'd like to give the users the ability to expand the spreadsheet to see what's going on, but collapse the extra data for printing purposes. MS Support said that this functionality wasn't available (but it should be), so I'm curious if anyone has ideas for a good workaround? The spreadsheet can be grabbed from www.realrobert.com/foreclosure/worksheet.xls - password is blank to unlock. Any feedback would be greatly appreciated! |
#3
|
|||
|
|||
I had a very similar requirement with grouped columns.
Dave Peterson (thanks agin Dave) posted the following and it worked fine for me. The only qualifiication is that someone who knows what they are doing can edit the macro and see your password - not a problem in my case as I am trying to protect the sheet from novices no experts! 'If you already have the outline applied, you can protect the worksheet in code (auto_open/workbook_open??). Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With End Sub It needs to be reset each time you open the workbook. (excel doesn't remember it after closing the workbook.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm' "Robert Slattery" wrote: I've created a worksheet that will be distributed to 300+ users in an investment group. It contains formulas and computations in cells that could easily be overwritten by spreadsheet novices, so I've protected the worksheet so users can only edit the cells they need to for the sheet to return the info they need. The problem is: after protecting the worksheet, Excel won't allow the user to expand or collapse grouped rows that list detailed information on each step of the calculations. This is inconvenient because I'd like to give the users the ability to expand the spreadsheet to see what's going on, but collapse the extra data for printing purposes. MS Support said that this functionality wasn't available (but it should be), so I'm curious if anyone has ideas for a good workaround? The spreadsheet can be grabbed from www.realrobert.com/foreclosure/worksheet.xls - password is blank to unlock. Any feedback would be greatly appreciated! |
#4
|
|||
|
|||
You can actually protect the project from most people.
Next time you're in the VBE, select that project. Then Tools|VBAProject Properties|Protection Tab But this protection can be broken in a matter of seconds--if the user knows how. But it does keep most out. Colin_Bizfine wrote: I had a very similar requirement with grouped columns. Dave Peterson (thanks agin Dave) posted the following and it worked fine for me. The only qualifiication is that someone who knows what they are doing can edit the macro and see your password - not a problem in my case as I am trying to protect the sheet from novices no experts! 'If you already have the outline applied, you can protect the worksheet in code (auto_open/workbook_open??). Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With End Sub It needs to be reset each time you open the workbook. (excel doesn't remember it after closing the workbook.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm' "Robert Slattery" wrote: I've created a worksheet that will be distributed to 300+ users in an investment group. It contains formulas and computations in cells that could easily be overwritten by spreadsheet novices, so I've protected the worksheet so users can only edit the cells they need to for the sheet to return the info they need. The problem is: after protecting the worksheet, Excel won't allow the user to expand or collapse grouped rows that list detailed information on each step of the calculations. This is inconvenient because I'd like to give the users the ability to expand the spreadsheet to see what's going on, but collapse the extra data for printing purposes. MS Support said that this functionality wasn't available (but it should be), so I'm curious if anyone has ideas for a good workaround? The spreadsheet can be grabbed from www.realrobert.com/foreclosure/worksheet.xls - password is blank to unlock. Any feedback would be greatly appreciated! -- Dave Peterson |
#5
|
|||
|
|||
Expand/Collapse Grouped Data in Protected Worksheet
I have the same problem like Robert, but I seem not to understand the
instructions that you gave Robert. I’m not familiar with macro. Is there away it could be done directly with excel or do I have to learn macro. "Jim Cone" wrote: Robert, I modified your workbook slightly, added some code and a couple of buttons from the Forms toolbar. I believe it does what you want. 1. Insert a row just above "auction items", call it Details and format it the same as the Summary row. 2. Clear the outline 3. Add a button to cell B25 with the caption "Details" 4. Add a button to cell C25 with the caption "Summary" 5. Add a module to your workbook and insert the following code... '----------------------------------------------- Sub ShowTheDetails() ActiveSheet.Unprotect If Rows(27).Hidden = True Then Rows("27:43").Hidden = False Else Rows("27:43").Hidden = True End If ActiveSheet.Protect End Sub Sub ShowTheSummary() ActiveSheet.Unprotect If Rows(45).Hidden = True Then Rows("45:53").Hidden = False Else Rows("45:53").Hidden = True End If ActiveSheet.Protect End Sub '---------------------------------------------- 6. Assign ShowTheDetails macro to the "Details" button. 7. Assign ShowTheSummary macro to the "Summary button. I will be glad to send you the workbook with the changes in it, if you would like to see it. Remove XXX from my email address. Regards, Jim Cone San Francisco, USA XX "Robert Slattery" wrote in message ... I've created a worksheet that will be distributed to 300+ users in an investment group. It contains formulas and computations in cells that could easily be overwritten by spreadsheet novices, so I've protected the worksheet so users can only edit the cells they need to for the sheet to return the info they need. The problem is: after protecting the worksheet, Excel won't allow the user to expand or collapse grouped rows that list detailed information on each step of the calculations. This is inconvenient because I'd like to give the users the ability to expand the spreadsheet to see what's going on, but collapse the extra data for printing purposes. MS Support said that this functionality wasn't available (but it should be), so I'm curious if anyone has ideas for a good workaround? The spreadsheet can be grabbed from www.realrobert.com/foreclosure/worksheet.xls - password is blank to unlock. Any feedback would be greatly appreciated! |
#6
|
|||
|
|||
Expand/Collapse Grouped Data in Protected Worksheet
I am using this it works ok but I'm running into a code signing issue now.
Shouldn't I be able to sign the macro and have other employees open it without having to obtain a certificate from a "trusted CA" its for internal use only anyway Option Explicit Sub workbook_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With End Sub |
#7
|
|||
|
|||
Expand/Collapse Grouped Data in Protected Worksheet
I'm running on Excel 2007 and I need to enable the end users to
expand/collapse columns in a protected sheet Instead of adding VBA language and buttons to run the script backend to ungroup/group columns, is there any other ways in Excel that will facilitate the same process? Any feedback will be greatly appreciated |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Format on data to import to Access tables? (I need your advice) | Niklas Östergren | General Discussion | 5 | December 13th, 2004 02:54 PM |
Is this possible with Excel Chart? | q582gmzhi | Charts and Charting | 1 | September 8th, 2004 03:33 AM |
Refresh data in Protected worksheet | Kevin T | General Discussion | 3 | July 15th, 2004 09:51 AM |
Mial merge data base problems | Rachael | Mailmerge | 16 | May 21st, 2004 06:22 PM |