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

"Anthony Harding" <(E-Mail Removed)> wrote in
message news:928A641F-58A8-458D-9BCB-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> "Anthony Harding" <(E-Mail Removed)> wrote in
> message news:928A641F-58A8-458D-9BCB-(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >
> > "Anthony Harding" <(E-Mail Removed)> wrote in
> > message news:928A641F-58A8-458D-9BCB-(E-Mail Removed)...
> >> 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" <(E-Mail Removed)> wrote in
message news:8313BD90-9B1B-48BF-A575-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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
>> >
>> > "Anthony Harding" <(E-Mail Removed)> wrote in
>> > message news:928A641F-58A8-458D-9BCB-(E-Mail Removed)...
>> >> 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.
>> >
>> >
>>
>>
>>