Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 19th 14, 10:04 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2013
Posts: 13
Default Chartobjects protected but it can still be deleted, edited and movedby the user (yes, the sheet is protected)

.... .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.ProtectFormattin g = 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

  #2   Report Post  
Old December 19th 14, 10:48 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2007
Posts: 230
Default Chartobjects protected but it can still be deleted, edited andmoved by the user (yes, the sheet is protected)

On 19/12/2014 09:04, tskogstrom wrote:
... .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.


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.ProtectFormattin g = 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

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.

--
Regards,
Martin Brown
  #3   Report Post  
Old December 19th 14, 11:12 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2013
Posts: 13
Default Chartobjects protected but it can still be deleted, edited andmoved by the user (yes, the sheet is protected)

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
  #4   Report Post  
Old December 19th 14, 11:15 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2013
Posts: 13
Default Chartobjects protected but it can still be deleted, edited andmoved by the user (yes, the sheet is protected)

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
  #5   Report Post  
Old December 19th 14, 11:28 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2007
Posts: 230
Default Chartobjects protected but it can still be deleted, edited andmoved by the user (yes, the sheet is protected)

On 19/12/2014 10:12, tskogstrom wrote:
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.


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.

--
Regards,
Martin Brown


  #6   Report Post  
Old December 19th 14, 12:56 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2013
Posts: 13
Default Chartobjects protected but it can still be deleted, edited andmoved by the user (yes, the sheet is protected)

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
My macro doesn't work if sheet is protected but it works if not protected [email protected] Excel Programming 4 February 11th 14 06:30 PM
Sheet protected - user cannot interact JLR-Mart Excel Programming 3 June 9th 08 05:52 PM
Tabbing thru User-Edit Cells in Protected Sheet ConfusedNHouston Excel Discussion (Misc queries) 2 May 13th 08 11:44 PM
Allow rows to be inserted and edited in a protected worksheet NoviceinEXCEL Excel Worksheet Functions 4 December 19th 06 05:41 AM
Activate ChartObjects on protected sheet?? RAP Excel Programming 0 August 26th 05 05:45 AM


All times are GMT +1. The time now is 07:27 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017