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.

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 Whe=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

"Anthony Harding" wrote in
message ...
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.

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"NickHK" wrote in message
...
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 Whe=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

"Anthony Harding" wrote in
message ...
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.

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" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"NickHK" wrote in message
...
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 Whe=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

"Anthony Harding" wrote in
message ...
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.

That's what I do.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Anthony Harding" wrote in
message ...
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" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"NickHK" wrote in message
...
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 Whe=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

"Anthony Harding" wrote in
message ...
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.