pop up window and protection of sheet

[Guest]

Hi,
I have a sheet with various data and graphs that is protected by a VBA
program when the sheet is activated:
With ActiveSheet
.EnableSelection = xlNoRestrictions
.Protect Contents:=True, password:="password", DrawingObjects:=True,
UserInterfaceOnly:=False
End With

The problem is the following:
I need to unprotect the sheet to do certain manipulations with VBA before I
get to display a graph using pop windows (the graphs are also locked by
protection). The problem is that when the user close the pop up window, the
sheet is unprotected. (writing the protection code underneath the popup
window code does not work. I think that the sheet is not recognised as active
anymore. and as I need this to work on several sheets, regardless of their
names, I cannot use the sheet name in the protection code)

'unprotect sheet
ActiveSheet.Unprotect password:="password"
.....
lots of VBA lines
....
'pop up window for graph
ActiveSheet.ChartObjects(3).Activate
ActiveChart.ChartArea.Select
ActiveChart.ShowWindow = True

Any idea very welcome.
Thanks

 

[Guest]

When protecting, try setting UserInterfaceOnly to True. This means that the
code can run free in the background

 

[Jim Rech]

I need to unprotect the sheet to do certain manipulations with VBA

The purpose of the UserInterfaceOnly parameter is to make that unnecessary.
So try setting that to True before going any farther.

UserInterfaceOnly:=True is not effective for every macro action however so
if it is still necessary to unprotect the sheet (and if the real problem
with re-protecting the sheet is that the formerly active sheet is no longer
the active sheet) revise your code along these lines:

Dim CurrSheet as Worksheet
Set CurrSheet = ActiveSheet
CurrSheet.Unprotect password:="password"
| ....
| lots of VBA lines
| ...
| 'pop up window for graph
CurrSheet.ChartObjects(3).Activate
ActiveChart.ChartArea.Select
ActiveChart.ShowWindow = True
CurrSheet.Protect ...etc.

--
Jim
| Hi,
| I have a sheet with various data and graphs that is protected by a VBA
| program when the sheet is activated:
| With ActiveSheet
| .EnableSelection = xlNoRestrictions
| .Protect Contents:=True, password:="password", DrawingObjects:=True,
| UserInterfaceOnly:=False
| End With
|
| The problem is the following:
| I need to unprotect the sheet to do certain manipulations with VBA before
I
| get to display a graph using pop windows (the graphs are also locked by
| protection). The problem is that when the user close the pop up window,
the
| sheet is unprotected. (writing the protection code underneath the popup
| window code does not work. I think that the sheet is not recognised as
active
| anymore. and as I need this to work on several sheets, regardless of their
| names, I cannot use the sheet name in the protection code)
|
| 'unprotect sheet
| ActiveSheet.Unprotect password:="password"
| ....
| lots of VBA lines
| ...
| 'pop up window for graph
| ActiveSheet.ChartObjects(3).Activate
| ActiveChart.ChartArea.Select
| ActiveChart.ShowWindow = True
|
| Any idea very welcome.
| Thanks
|
|
| --
| caroline