Issue allowing users to copy chart but forbidding to cut it

frizzipazzi

New Member
Joined
Oct 10, 2018
Messages
5
I have an excel sheet containing several charts & tables, the sheet is protected and all charts are locked.
I would like to make it possible for the users to copy the charts. Either by right clicking and selecting copy or by triggering a macro at when clicking or double clicking a chart.
I've tried playing around with the locked property of a chart & the <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">ActiveChart.ProtectSelection</code>but if the chart is unlocked or selectable the users are still able to cut the chart away from the sheet.
Assigning a macro to the chart also doesn't solve the problem as I haven't found a way to find out what chart has been clicked (and if possible I would avoid having to write a separatate sub for each chart..).
I've tried using the <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Chart1_BeforeDoubleClick</code> event but I can't get it to work (does it only work in a chart worksheet?)
thanks!


I have an excel sheet containing several charts & tables, the sheet is protected and all charts are locked.
I would like to make it possible for the users to copy the charts. Either by right clicking and selecting copy or by triggering a macro at when clicking or double clicking a chart.
I've tried playing around with the locked property of a chart & the <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">ActiveChart.ProtectSelection</code>but if the chart is unlocked or selectable the users are still able to cut the chart away from the sheet.
Assigning a macro to the chart also doesn't solve the problem as I haven't found a way to find out what chart has been clicked (and if possible I would avoid having to write a separatate sub for each chart..).
I've tried using the <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Chart1_BeforeDoubleClick</code> event but I can't get it to work (does it only work in a chart worksheet?)

thanks!

 
Last edited by a moderator:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
You could use the proceedure
-User Selects a Chart
-User clicks a button
-Chart is copied

That way Selection would be the ChartArea object that the user chose in step 1.

BTW, a Chart object doesn't have a Click event, but it does have BeforeDoubleClick and BeforeRightClick events.
 
Last edited:
Upvote 0
Cross posted https://stackoverflow.com/questions/52740414/how-to-unlock-a-chart-and-forbid-cutting-it#

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0
You could use the proceedure
-User Selects a Chart
-User clicks a button
-Chart is copied

That way Selection would be the ChartArea object that the user chose in step 1.

BTW, a Chart object doesn't have a Click event, but it does have BeforeDoubleClick and BeforeRightClick events.

thanks Mike!
I had thought to that solution but I was hoping to find a solution not needing 2 actions :)

what is possible to do is to assign a macro to a chart: when we click on it the macro runs (not via an event)

the BeforeDoubleClick and BeforeRightClick events can be triggered from a chart in a sheet? I haven't managed to :(

My apology for cross posting and not mentioning the cross posting!
 
Upvote 0
Insert a Class Module and put code like this in it.
Code:
' in Class1

Public WithEvents myChart As Chart

Private Sub myChart_BeforeDoubleClick(ByVal ElementID As XlChartItem, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
    MsgBox myChart.Name
    Cancel = True
End Sub

Then you can assign a chart to that class with code like this in a normal module.
Code:
' in normal module

Public myFunkyChartThing As Class1

Sub test()
    Set myFunkyChartThing = New Class1
    
    Set myFunkyChartThing.myChart = ActiveSheet.ChartObjects(1).Chart
End Sub
If you run Test (once), thereafter double clicking on the chart will trigger the routine.
 
Upvote 0
Insert a Class Module and put code like this in it.
Code:
' in Class1

Public WithEvents myChart As Chart

Private Sub myChart_BeforeDoubleClick(ByVal ElementID As XlChartItem, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
    MsgBox myChart.Name
    Cancel = True
End Sub

Then you can assign a chart to that class with code like this in a normal module.
Code:
' in normal module

Public myFunkyChartThing As Class1

Sub test()
    Set myFunkyChartThing = New Class1
    
    Set myFunkyChartThing.myChart = ActiveSheet.ChartObjects(1).Chart
End Sub
If you run Test (once), thereafter double clicking on the chart will trigger the routine.

Wow, that's very clever!
However, I need to keep the chart unlocked in order to run this (or at least allow selection) -- but in both cases the chart can be cut...
 
Upvote 0
Thanks!
I have found another solution that solved my issue
here

I have assigned the subroutine below to all my charts:

Code:
Sub Chart_Click()


    Dim chartName As String


    chartName = ActiveSheet.Shapes(Application.Caller).Name
    ActiveSheet.ChartObjects(chartName).Copy
    MsgBox "Chart copied."


End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

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
Back
Top