chart legend position

[Mark.B]

Does anyone know how I can change the position of a legend (within a chart) even though the worksheet is protected.

I have used the following code (which works fine when the workseet is unprotected):

ActiveSheet.ChartObjects("Chart3").Chart.Legend.Top = 500


I have also tried:

With ActiveSheet.ChartObjects("Chart3")
.Chart.Legend.Top = 500
End With

and:

With ActiveSheet.ChartObjects("Chart3").Chart
.Legend.Top = 500
End With

and also:

With ActiveSheet.ChartObjects("Chart3").Chart.Legend
.Top = 500
End With

to no avail.

any help would be greatly appreciated.

Thanks

Mark

 

[tusharm]

Does anyone know how I can change the position of a legend (within a chart) even though the worksheet is protected.

I have used the following code (which works fine when the workseet is unprotected):
{snip}

The purpose behind protecting a worksheet is to *protect* it. So, if you must make the changes you want to make, unprotect the sheet first and when done protect it. Obviously, you can do the needful through the code itself.

 

[Mark.B]

Thanks Tusharm,

Can you explain why then, I can manipulate the chart itself e.g height, width & position (whilst the worksheet is *protected*), but I cannot manipulate items within the chart ?

Regards

Mark

 

[Mark.B]

Can anyone help ?

 

[tusharm]

I don't know. Maybe, it's a bug. Maybe, it's a feature. I would lean towards the former. Nonetheless, how does it matter? You cannot do what you want while the sheet is protected.

 

[Mark.B]

Thanks Tusharm, although I think that you have missed the point that I was making in my response.

Firstly, you can manipulate items within a protected worksheet, contrary to your response which implies that it is not possible (e.g. "The purpose behind protecting a worksheet is to *protect* it").

Second, if the above is true (which it is), then can anyone describe a method that would enable me to manipulate an item within an item (e.g. a legend within a chart) on a protected worksheet (a method which you or I do not fully understand).

If the answer to that question is still 'NO', then fair enough. I will give up trying.

Regards

Mark

 

[tusharm]

OK, I will leave you to your endeavors and stop monitoring this topic for further responses.

 

[Jon Peltier]

The shape containing the chart can have a different level of protection from the chart itself. Double click on the chart area of a chart, and click on the Properties tab. If the Locked checkbox is checked and the sheet protected, you can't even select the chart. If the checkbox is unchecked, you can in general do what you want with the chart.

However, there are some settings in VBA that will protect a chart, whether or not the sheet is protected. None of these settings seem to take a password. For example:

ActiveChart.ProtectFormatting = True

allows you to select chart elements and change chart data, but not move or format chart elements.

ActiveChart.ProtectData = True

allows you to reformat the chart, but not reassign the source data to other cells. If the current source data's values change, the chart changes.

ActiveChart.ProtectSelection = True

prevents the user from selecting the chart or its elements, but you can still use VBA to format these elements.

 

[tusharm]

The shape containing the chart can have a different level of protection from the chart itself. Double click on the chart area of a chart, and click on the Properties tab. If the Locked checkbox is checked and the sheet protected, you can't even select the chart. If the checkbox is unchecked, you can in general do what you want with the chart.
{snip}

Jon, the problem is in the other direction. Even when a chart and sheet are configured so that the chart cannot be selected through the UI, certain changes through VBA still work. These appear related to the chartobject, not the chart (Left, Top, Width, Height are the few I tested).

It's almost as though VBA ignores sheet/chart protection when dealing with certain properties of an object. You can do the same with a shape such as a rectangle that can be done with a chartobject. Create a rectangle, set its protection to Locked, protect the worksheet and VBA statements that affect the object's position (I just tested Left) continue to work. Of course, through the UI one cannot even select the rectangle.

 

[Jon Peltier]

Tushar -

What I described after the snip is one way to see the described behavior. What you've described is more likely the case, since it's easier to replicate than the fancy protection properties of a chart. I hadn't tested the size/position properties of the shape, assuming that sheet protection prevented me from changing them, so I missed it.

Another interesting thing I thought of was the ability of VBA to protect the worksheet for the UI only, so that VBA is not blocked by sheet protection. However, the UI only setting has no effect on charts; the sheet has to be unprotected before VBA can change the charts.