Unprotect WS on clicking Group

[Jen]

Hi There,

Would it be possible to unprotect my sheet when I push on the +'s and -'s
for grouped rows (columns)
&Reprotect when a cell/range on the sheet has been selected?
I have no idea on where to start this one .. I am sure it is some
worksheet-event code but ...


Brgds Jen

 

[Guest]

Try the _SelectionChange(Target As Range) event. You could check for number
of rows/columns or cells selected to see whether to unprotect/protect it.
But since your second option is "reprotect when a cell/range" has been
selected, if a range has been selected it's going to take some examination of
the address of the selection to tell whether you've selected one or more
entire rows/columns or just a range that extends over several of them.

As you've laid things out, you're not going to accomplish much - I think
you've not told us about something, Jen:
Select several rows/columns = unprotect the sheet, and then
When 'individual' cell/range on the sheet is selected, put it back into
protected state?
Perhaps you want to put it back into protected state after a _Change to
values on the sheet takes place?

 

[Jen]

Hi Jlatham,

I've a sheet that has locked cells (the ones with the formulas in) ... in
order not to overwrite them "accidentally" I locked the sheet.

I have set some "groupings" to show/hide rows (columns)... but this I cannot
use because the sheet is locked (with an "empty" password).

In order to use the "groupings" (the + & - signs) I have to unlock the
sheet...

Once I have set the "right" groupings" (I use this method for showing/not
showing series in my graphs...) I will return to my worksheet, at that point
I would love to see the sheet re-protected ...

Hope this expalins better what I want to achieve?? Crossing fingers

Jen

 

[Guest]

when you protect the sheet, Use the enableoutlining property (and include the
UserInterFaceOnly property as well.

Activesheet.Protject password:="ABC", UseInterFaceOnly:=True
Activesheet.EnableOutlining = True


This is none persistent, so you must run code to set it each time the
workbook is opened.

 

[Jen]

Hi Tom,

I used:
Sub jen()
ActiveSheet.Protect Password:="ABC", UseInterFaceOnly:=True
ActiveSheet.EnableOutlining = True
End Sub

Ehmmm, When I run this sub .... it breaks on the
"UseInterFaceOnly:=True"-part!
So I removed that part and ran:

Sub jen()
ActiveSheet.Protect Password:="ABC"
ActiveSheet.EnableOutlining = True
End Sub

Which properly protects the sheet ...but still does not allow to push the
outlines!

Then I tried something like:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Locked Then
Protect Password:="ABC"
EnableOutlining = True
Else
Unprotect Password:="ABC"
EnableOutlining = True
End If
End Sub

It obviously still does not allow to use the outlines ofcourse.
Would there be a way to tell if the "Target"-selection has more than 1 cell
but at least 1 has a locked cell to invoke the
Protect Password:="ABC"
EnableOutlining = True -part?

Like:
'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'If Target.HAS_ONE_or_MORE_LockedCELLs Then
' Protect Password:="ABC"
' EnableOutlining = True
' Else
' Unprotect Password:="ABC"
' EnableOutlining = True
' End If
'End Sub


Jen

 

[Guest]

Unfortunately, there was a typo in the spelling of UserInterfaceonly

Sub ABCDE()
ActiveSheet.Protect Password:="ABC", UserInterFaceOnly:=True
ActiveSheet.EnableOutlining = True
End Sub

worked fine for me. You don't need put in a password unless you want to
have one. If you do use a password, you will then need it to make any
changes to sheet protection.

 

[Jen]

Hi Tom,
Thanks ! ... it worked. Of course it did
Jen

 

[Jen]

Hi Tom,

A related question .... I have a a control (checkbox eg) from the
Forms-toolbar on my sheet.
When the sheet is protected I cannot use those controls, (my Outlines work
) do you have some other magic to allow the functionality of the
Forms-controls on a protectd sheet?

Jen