Allowing Chart creation in protected worksheets sheets using VB co

[Anthony Harding]

I'm currently struggling with a spreadsheet that generates a table of output
data in a worksheet using VB in Excel 2003. I want to prevent the user from
editing the data created, but still allow the user to create a chart from
that data in the worksheet. I don't want to create the chart in advance, as
the size of the table is not fixed.

I've found that using the Potect Worksheet command from the excel menus
allows me to password protect the sheet, and allow the editing of objects.
Once protected, the worksheet allows the creation, editing and deletion of
charts. This is great: exactly what I'm looking for.

My problems begin when I re-run the function that crates the output data.
To access the worksheet I'm using the VB protect method with
UserInterfaceOnly:=True. However, this seems to overwrite the protection
settings, no longer allowing chart creation/deletion.

Online help for the Protect method suggests that it is only possible to
permit editing of drawing objects, not all objects like you can from the
Excel menus. Is this the case? If so, does anyone know of any way round
this?

Many thanks for your time.

Anthony.

 

[NickHK]

Anthony,
This works for me in XL2002:

Sub Macro1()

Range("D6").Select
'Protect
ActiveSheet.Protect DrawingObjects:=False, Contents:=True,
Scenarios:=True, UserInterfaceOnly:=True
'Unlocked cell
Range("E7").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("D5:E12")
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
ActiveSheet.Shapes("Chart 4").IncrementLeft 353.25
ActiveSheet.Shapes("Chart 4").IncrementTop 103.5

'This is a locked cell
Range("A1").Value = "Done"
End Sub

NickHK

 

[Jon Peltier]

Note that the UserInterfaceOnly setting is lost on closing the file, so you
must restore it when the file is reopened.

Also, in a case like this, it may be just as easy to simply unprotect the
sheet, do the chart stuff, then reprotect the sheet.

- Jon

 

[Anthony Harding]

Thanks for your replies.

This does work for me, but I was actually hoping to allow other users to
create charts using the chartwizard on the userinterface, without having to
allow them to unprotect the sheet.

Perhaps it would be easier for me to ask for the chart inputs and then write
a routine to create the required chart?

Anthony.

 

[Jon Peltier]

That's what I do.

- Jon