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,487 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 8

Author Comment

by:csoussan
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 17

Accepted Solution

by:
andrewssd3 earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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:teylyn
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Outlook Free & Paid Tools
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now