Solved

Can't Change The Size of the Plot Area and Legend In An Unlocked Chart Embedded In An Excel 2010 Protected Sheet

Posted on 2011-09-22
9
1,546 Views
Last Modified: 2012-08-13
My users are unable to change the size of the plot area and legend in an unlocked chart that is embedded in a protected sheet.  
 
Facts:
I have a sample file attached that has two embedded charts (Chart #1 and Chart #2) on Sheet1.
Sheet1 is protected but the charts are unlocked (Format Chart Area --> Properties --> uncheck Locked).
They can format the data series, the axis, the legend, the plot area, etc. - but, they can't change the size of the plot area or the legend.

Notes:
Sheet1's Password is blank - in other words, there is no password
The Plot Area and Legend are currently formatted with a blue dash line for easy visibility

Question: How can my users change the size of the Plot Area and Legend of Chart #1 so that it looks similar to Chart #2 without giving them the password to unprotect the worksheet? I have hundreds of charts of varying sizes that need to be edited so, I need a solution that can work on charts of all shapes and sizes.

Thanks for your time.  Sample-Chart.xlsm
0
Comment
Question by:csoussan
9 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36583432
This isn't an "answer", but it doesn't appear that one can change the size/aspect ratio of the Plot area when a sheet is protected.  Oddly enough, one can change almost every other property (ex. background, border).  

The only workaround - and I'm not happy about even mentioning it - is that the chart object itself  CAN be resized.  If you removed borders from the chart object, it could be widened so that the plot area is proportionally widened as well.

Looks like you found a "hidden feature" in Excel; Congratulations! :-)

-Glenn
0
 
LVL 8

Author Comment

by:csoussan
ID: 36584743
Thanks for the thought but changing the the size of the chart object is not an option.  I'm hoping someone knows of a way to do it through the GUI or maybe knows which chart object can be referenced via VBA to unlock the size aspect of the plot area or legend.
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36586284
Yes this definitely looks like a feature.  Is it an option to unprotect the sheet while the macro is running, then protect it again.  Obviously it depends whether it's password protected and whether you have the password, e.g.:

Sub plotTest()

    Dim c As Chart
    Set c = ActiveChart
    
    ActiveSheet.Unprotect
    
    With c.PlotArea
        .Position = xlChartElementPositionAutomatic
    End With
    
    ActiveSheet.Protect

End Sub

Open in new window

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 8

Author Comment

by:csoussan
ID: 36586416
Thanks for the code andrewssd3.  Unfortunately, it won't work for my situation because we have hundreds of charts of varying sizes that need to be edited and the plot area and legend will differ depending on the chart.  I need a solution that allows the users to determine the sizes for charts of all shapes and sizes.

0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 500 total points
ID: 36586477
OK - so you want them to be able to drag the handles around but not have the sheet unprotected while they do it?  If so, I don't think that's going to be possible.

If not, you could still do it the way I suggested if you could present them with a dialog box allowing them to specify the width, height, top and left for the plotarea and/or the legend - they would not be able to do anything on the unprotected sheet while a modal dialog box was up. Alternatively, if you could specify a rule (even quite a complex one) for the size of the plotarea and/or legend you could code this based on the sizes of the chart, and apply it automatically to all. E.g. something like make the plot area 85% of the width of the chart area, centered in the chartarea, or if there is a legend place it in the top left and shrink the plot area by 10%, etc... I have done similar things to this before.
0
 
LVL 8

Author Comment

by:csoussan
ID: 36586617
Unless it's an oversight on Microsoft's part - and that never happens ;) - there should be a way of unprotecting the sizing aspect of the plot area and legend.  It doesn't matter if it's through the GUI or only referencing the object through VBA (lock=false??).  If it can be unlocked - like all the other chart formatting options - I can leave the sheet protected.
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36586733
I tried various combination of the locking options thruogh the gui, but nothing seemed to allow that sort of change - the Allow editing objects looks like a hopeful one, but doesn't work.  In vba it seems like all the properties and methods of the plotarea object raise exceptions when the sheet is protected, so I'm not sure where you can go from here, unless along the lines I suggested before.
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 37412258
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question