• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Charts can be moved by anybody in my excel dashboard, want to prevent that from happening.

1) Charts can be moved by anybody in my excel dashboard, want to prevent that from happening. I have used the format chart area - properties - dont move or size with cell however it does not work and I am able to still drag it across.

2) When I lock only the area where charts are present on my excel dashboard, leaving aside the slicer, I am not able to use the Slicer either and everything get locked and I cant do anything.

Can somebody please help me.

Thanks a lot in advance.
 
Hopefully this will give you a start :

"
  • ActiveChart.ProtectSelection = True
    This prevents selection of the chart or any of its elements. If an embedded chart is protected in this way, it cannot be selected (activated), so it must be referenced through its parent worksheet and chart object to unprotect:
    ActiveSheet.ChartObjects(1).Chart.ProtectSelection = False

"

https://peltiertech.com/Excel/ChartsHowTo/ChartProtection.html
 
Hi Logit,

Thanks a lot for your reply; unfortunately, I dont know to write code, would you be able to write a code for me please that I can copy in my excel file.
 
You would have to attach your workbook.

Understand, I may not be successful but I'll give it a good try.
 
Hi Logit,

Attached herewith is the dashboard I have prepared.

1) Charts can be moved by anybody in my excel dashboard, want to prevent that from happening. I have used the format chart area - properties - dont move or size with cell however it does not work and I am able to still drag it across.

2) When I lock only the area where charts are present on my excel dashboard, leaving aside the slicer, I am not able to use the Slicer either and everything get locked and I cant do anything.
 

Attachments

  • Pursuits_Tracker_v0.4.xlsm
    201.2 KB · Views: 13
.
Code:
Private Sub Worksheet_Activate()
    With ActiveSheet.Shapes("Chart 5")
        .Left = Range("F2").Left
        .Top = Range("F2").Top
    End With
End Sub

There are additional settings for Height and Width. Do a google for Chart Settings.
I place the above in the Sheet1Worksheet_Activate module. When the user leaves Sheet 1
then returns, the chart will return to it programmed posiiton.

Another method is to lock the worksheet but I believe you indicated that was interfering with
data updating. You would need to unlock the sheet before updating data if that is the case.

Found a number of websites that talked about the OPTIONS settings for locking the chart to
the cell but of course that doesn't work. The user can still move the chart.

This is all I found. Hope something here helps you.
 
Ok .. I understand. Regretfully it appears that Microsoft has not provided a means of locking the charts in place other
than locking down the entire worksheet.

If you are doing something in VBA code to update data that affects the charts, you can include a line or two of VBA
code that unlocks the sheet first. Then after all code has been run, at the end of the macro, include necessary code
to lock the sheet again.

Hope this helps.
 
Back
Top