Issue allowing users to copy chart but forbidding to cut it

[frizzipazzi]

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!

 

[frizzipazzi]

My apologies for the formatting
can't go back to edit the post

 

[mikerickson]

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.

 

[Fluff]

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.

 

[frizzipazzi]

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!

 

[mikerickson]

Insert a Class Module and put code like this in it.

' 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.

' 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.

 

[frizzipazzi]

Insert a Class Module and put code like this in it.

' 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.

' 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...

 

[mikerickson]

I envision that the double click event would
- unprotect the sheet
- copy the chart
- re-protect the sheet.

 

[frizzipazzi]

Thanks!
I have found another solution that solved my issue
here

I have assigned the subroutine below to all my charts:

Sub Chart_Click()


    Dim chartName As String


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


End Sub