Chartobjects protected but it can still be deleted, edited and movedby the user (yes, the sheet is p

  • Thread starter Thread starter tskogstrom
  • Start date Start date
T

tskogstrom

.... .Chart.ProtectFormatting = True
.... .Chart.ProtectData = True
.... .Chart.ProtectSelection = False

I use these protections in a protected worksheet for my chartobjects in my code in a excel 2010 xlsm
- It has worked fine for years in 2010.

Now, when I test it with Excel 2007 the code works fine too. No errors, and in the GUI the chartobject protection checkbox is locked. BUT:

In 2007 the chartobjects still are editable for the user and I can delete and move them however I like. The sheet is still protected.

For the sheet protection I use this code:

....
...
If cnReport.ProtectContents = False Then
cnReport.Protect Password:=shtPassX, DrawingObjects:=False, Contents:=True, Scenarios:=True
cnReport.EnableSelection = xlNoRestrictions
End If
...
....


I cannot understand it. Any bug known? Workarounds?

/Thanks and merry cristmas



##### Full code below for the interested ###

Sub ProtectCharts(sCrt As String)
'---------------------------------------------------------------------------' Procedure : ProtectCharts
' Purpose :
'---------------------------------------------------------------------------
Debug.Print " ### START ProtectCharts in ReportSpecificCode"
On Error GoTo ProtectCharts_Error
Application.EnableCancelKey = xlDisabled

cnReport.ChartObjects(sCrt).Chart.ProtectFormatting = True
cnReport.ChartObjects(sCrt).Chart.ProtectData = True
cnReport.ChartObjects(sCrt).Chart.ProtectSelection = False

On Error GoTo 0

Exit Sub

ProtectCharts_Error:
Debug.Print " -ERROR- " & Err.Number & " (" & Err.Description & ") in procedure ProtectCharts of VBA Document cnReport"

End Sub



Sub CallProtectCharts()
'---------------------------------------------------------------------------
' Procedure : CallProtectCharts
' Purpose ->
'---------------------------------------------------------------------------
Debug.Print " ### START CallProtectCharts in ReportSpecificCode"
On Error GoTo CallProtectCharts_Error
Application.EnableCancelKey = xlDisabled

If ReportUpdate = True Then
UppdateSplashReport ("Protect Report Charts")
Application.EnableCancelKey = xlDisabled
End If
Debug.Print " START->>>> CallProtectCharts in cnReport"

ProtectCharts ("CRT_Pie_Expandables_RVA")
ProtectCharts ("CRT_Pie_Ben_RVA")
ProtectCharts ("Chr_Bubb_Ben_RVA")
ProtectCharts ("Chr_Bubb_Exp_RVA")
ProtectCharts ("REP_Cashflow_RVA")
ProtectCharts ("RepChrt_BCARelRVA")
ProtectCharts ("RepCashFlowAlts")


On Error GoTo 0
Debug.Print " ooo EXIT CallProtectCharts in ReportSpecificCode"
Exit Sub

CallProtectCharts_Error:
Debug.Print "Error " & Err.Number & " (" & Err.Description & ") in procedure CallProtectCharts of Module ReportSpecificCode"

End Sub
 
... .Chart.ProtectFormatting = True
... .Chart.ProtectData = True
... .Chart.ProtectSelection = False

I use these protections in a protected worksheet for my chartobjects in my code in a excel 2010 xlsm
- It has worked fine for years in 2010.

Now, when I test it with Excel 2007 the code works fine too. No errors, and in the GUI the chartobject protection checkbox is locked. BUT:

In 2007 the chartobjects still are editable for the user and I can delete and move them however I like. The sheet is still protected.
Click to expand...

I have a sneaky feeling allowing objects to be selected will be
responsible for the loophole. I haven't seen this fault myself.
For the sheet protection I use this code:

...
..
If cnReport.ProtectContents = False Then
cnReport.Protect Password:=shtPassX, DrawingObjects:=False, Contents:=True, Scenarios:=True
cnReport.EnableSelection = xlNoRestrictions
End If
..
...


I cannot understand it. Any bug known? Workarounds?

/Thanks and merry cristmas



##### Full code below for the interested ###

Sub ProtectCharts(sCrt As String)
'---------------------------------------------------------------------------' Procedure : ProtectCharts
' Purpose :
'---------------------------------------------------------------------------
Debug.Print " ### START ProtectCharts in ReportSpecificCode"
On Error GoTo ProtectCharts_Error
Application.EnableCancelKey = xlDisabled

cnReport.ChartObjects(sCrt).Chart.ProtectFormatting = True
cnReport.ChartObjects(sCrt).Chart.ProtectData = True
cnReport.ChartObjects(sCrt).Chart.ProtectSelection = False

On Error GoTo 0

Exit Sub

ProtectCharts_Error:
Debug.Print " -ERROR- " & Err.Number & " (" & Err.Description & ") in procedure ProtectCharts of VBA Document cnReport"

End Sub
Click to expand...
I'd be inclined to set protectselection = True as well in XL2007 to see
if that alters this behaviour.

I can't say I have noticed any protection issues with drawing objects
going from 2003 to 2007. Loads of bad race conditions and random
reassignment of the parameters of objects but nothing like this.

But I have never moved any code back from 2010 to 2007.
 
Yes, .Chart.ProtectSelection = True works, but will make it impossible for the user to paste the diagrams pptx and docx files.

This is mandatory, so unfortunately it is not a solution.

Thanks anyway,
/ tskogstrom
 
I'll check it out back home, but users' copy paste chartobjects is mandatory for the application. So, anyhow, it is not a solution.

Thanks anyway,
tskogstrom
 
Yes, .Chart.ProtectSelection = True works, but will make it impossible for the user to paste the diagrams pptx and docx files.

This is mandatory, so unfortunately it is not a solution.
Click to expand...

Not necessarily - you could provide the user with a button that allows
them to copy the graph. I'll have to have a play to see if I have a
similar vulnerability in one of my data charting codes that allows the
user to move cursors to set integration limits. Noone has reported a
fault but then unless you selected and deleted one you might never know.
 
Yes, actually, I am right now investigating how much work that wouold be. Need to find some code snippets and adapt. If you can support me someway, i love it. If you have the need to fix this too, we can help eachother.

Is some work because there are about 15 chartobjects on the sheet, and without the possibility to select them, I had to offer a copy paste for all of them to eg. more to the right in the same sheet.

Also, i don't want a bunch of twin chartobjects remaining there, but I cannot delete them on worksheet_deactivate or workbook_deactivate, because if they want to paste them into a ppt they will be deleted after one window switch. Most probably, I had to wipe them out after closing the workbook ...

So there are a lot of work, I guess. All because of this Excel 2007 bug...

/thanks
tskogstrom
 
Back
Top