Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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,708 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

598 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