Can I unprotect / protect a shared workbook?

[Guest]

Dear all,

I have the following scripts that assign value & validate for specific column:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 20 Then
Sheet1.Unprotect "password"
If Len(Target.Value) = 0 Then
With Target(1, 0)
.Value = Cells(Target.Row, Target.Column - 7)
With .Validation
..........
End With
End With
End If
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:= _
True
Sheet1.Protect "password", , , , , , , , , , , , , True, True, True
End If

It's working properly in exclusive mode but failed when sharing the workbook
(Set it under the "Tools" manual).

Much appreciate for your any idea!!

Thanks in advance!

Florence

 

[Norman Jones]

Hi Florence,

See Limitations of shared workbooks' in Excel help.

Specifically, this article indicates that shared workbooks will not allow
the application or removal of sheet protection passwords.

 

[Guest]

Thx Norman,

It's understood. So I'm thinking of cancelation before entering into the
loop and share it again before leaving the loop.

I found an "ActiveWorkbook.SharedWorkspace" command for sharing workspace.
Is there any similiar method that can enable & disable workbook sharing?

Regards,

Florence

 

[Guest]

One more finding:

Even I can unprotect the worksheet, the "With Validation" cannot be executed
in the shared workbook.

So I think I need to be able to control the sharing inside the script.

Much appreciate for your any idea!!

Florence

 

[Norman Jones]

Hi Florence,

See the ProtectSharing method in VBA help.

 

[Guest]

Thanks agian Norman,

The script can be executed by "ActiveWorkbook.UnprotectSharing" when
entering into the loop and "ActiveWorkbook.ProtectSharing" before leaving the
loop.

But it generates another question: Such method is used to "Saves the
workbook and protects it for sharing.", it asks me about "file already exists
in this location, do you want to replace it?" for every cell changes. i.e. I
have 6000 rows in my spreadsheet so the user will be asked to replace the
file for 6000 times.

Is there any ways to ignore the dialogue box?

Regards,

Florence

 

[Norman Jones]

Hi Florence,

Firstly, my knowledge of Shared workbooks is severely limited as I never use
them - the limitations are such as to deter me.

That said, post the problemtic code section and I will look at it.

 

[Guest]

Helo Norman,

Here it is:

=====================

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False

' Check the column 19, set 0 at the column 18 if any options selected.
If Target.Column = 19 Then

ActiveWorkbook.UnprotectSharing sharingpassword:="123456"

If Len(Target.Value) = 0 Then
With Target(1, 0)
.Value = Cells(Target.Row, Target.Column - 5)
With .Validation
.................................
End With
End With
Else
With Target(1, 0)
.Value = 0
With .Validation
............................
End With
End With
End If

ActiveWorkbook.ProtectSharing Filename:=strFilePathName,
sharingpassword:="123456"

End If

=====================

Any idea?

Thx!

Florence

 

[Norman Jones]

Hi Florence,

To suppress the unwanted messages, try adding the line:

Application.DisplayAlerts =False

immediately *before*:

ActiveWorkbook.UnprotectSharing sharingpassword:="123456"

Similarly, add the line:

Application.DisplayAlerts = True

immediately *after* the line:

ActiveWorkbook.ProtectSharing Filename:=strFilePathName,
sharingpassword:="123456"

In my tests, I deleted :
Filename:=strFilePathName
in the above line.

In testing, this worked for me and suppressed the alert messages which you
complain about, although there is, necessarily, a brief, but perceptible,
pause while the file is saved in the background.