How to select/copy but not move/edit protected charts?

[Kelvin Stott]

I have protected some embedded charts in a worksheet as I don't want people to move them around or edit them, however this also prevents selecting and copying those charts, in case people want to copy and paste them into another document (e.g., powerpoint).

Is there any way I can protect embedded chart objects against moving and editing, but still allow people to select and copy them? Please?

 

[Kelvin Stott]

Sorry for the small bump/reminder ... any help with this, anyone, please?

 

[Kelvin Stott]

I still need some help with this, please!

I managed to find a potential workaround, by assigning a macro to copy each protected chart when you click on it:

Private Sub Chart1_Click()
 
    ActiveSheet.ChartObjects(1).Copy
 
End Sub

However, this simple macro has two problems:

1. For some reason, activating this macro to copy the chart initiates a complete recalculation, which takes several minutes. How can I suppress the recalculation when the chart is copied using this macro?

2. If I then paste the chart into Powerpoint, I can open the chart and see all the formulae in the protected worksheet, which I do not want. How do I restrict the copy function in the macro to copy the chart as an image or metafile, so that people can't access the underlying worksheet/formulae once they've pasted the chart into another application?

Again, any help would be much appreciated, please.

Thank you

 

[Kelvin Stott]

Never mind, I manage to figure out a solution without help, eventually. And in case anyone has the same issue:

Private Sub Chart1_Click()
 
    ActiveSheet.ChartObjects(1).CopyPicture
    MsgBox ("Chart copied to clipboard.  You can now paste this into another application.")
    
End Sub

 

[imashish]

Hey Kelvin, you are great thanks for posting the way around. I also needed this solution. I really appreciate the help. Thanks

 

[imashish]

Just wanted to add a help to this thread. The code works fine and copies the chart properly and the next step is if want to clear the clipboard i.e; to let the machine forget the copied selection the following code suits well-

Option Explicit 
Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long 
Public Declare Function EmptyClipboard Lib "user32" () As Long 
Public Declare Function CloseClipboard Lib "user32" () As Long 
 
Public Function ClearClipboard() 
    OpenClipboard (0&) 
    EmptyClipboard 
    CloseClipboard 
End Function 
 
Sub ccc() 
    Call ClearClipboard 
End Sub

the link for the above code is http://www.vbaexpress.com/kb/getarticle.php?kb_id=205#instr