Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Robert Slattery
 
Posts: n/a
Default 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   Report Post  
Jim Cone
 
Posts: n/a
Default

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   Report Post  
Colin_Bizfine
 
Posts: n/a
Default

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   Report Post  
Dave Peterson
 
Posts: n/a
Default

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   Report Post  
Joel
 
Posts: n/a
Default 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. Im 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 989
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 67
Default 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
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Expand/Collapse Grouped Data in Protected Worksheet

What do you mean by (auto_open/workbook_open??)




On Tuesday, April 12, 2005 at 10:11:01 PM UTC-4, 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!


  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default Expand/Collapse Grouped Data in Protected Worksheet

What do you mean by (auto_open/workbook_open??)

Workbook_Open is an Application 'event'.
May not always work, especially if the file is corrupt, because the
Application controls its functionality! The Application usually
disables events in corrupt files as well as for any other reason it
deems fit to do so during runtime.

Auto_Open is an AutoRunMacro 'procedure'.
This will pretty much always work even though it's also controlled by
the Application because procedures are managed differently than how
events are managed!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
refresh external data on a protected sheet ajf Excel Discussion (Misc queries) 0 March 11th 05 10:01 AM
Help with data not getting plotted Scott Ehrlich Excel Discussion (Misc queries) 2 January 25th 05 06:17 AM
How to use outline data (grouped rows) in a protected worksheet? biometris Excel Discussion (Misc queries) 1 January 17th 05 11:51 PM
How to use outline data (grouped rows) in a protected worksheet? biometris Excel Discussion (Misc queries) 0 January 17th 05 10:47 AM
Pulling data from 1 sheet to another Dave1155 Excel Worksheet Functions 1 January 12th 05 06:55 PM


All times are GMT +1. The time now is 10:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"