?
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
Medium Priority
?
1,642 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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 2000 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
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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

770 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