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,573 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

730 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