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

Kelvin Stott

Active Member
Joined
Oct 26, 2010
Messages
330
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?
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Kelvin Stott

Active Member
Joined
Oct 26, 2010
Messages
330
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:

Code:
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 :)
 
Last edited:

Kelvin Stott

Active Member
Joined
Oct 26, 2010
Messages
330
Never mind, I manage to figure out a solution without help, eventually. And in case anyone has the same issue:

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

:)
 

imashish

New Member
Joined
Apr 23, 2012
Messages
11
Hey Kelvin, you are great thanks for posting the way around. I also needed this solution. I really appreciate the help. Thanks
 

imashish

New Member
Joined
Apr 23, 2012
Messages
11
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-
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,326
Messages
5,595,517
Members
413,995
Latest member
waealu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top