Can I unprotect / protect a shared workbook?

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

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.

---
Regards,
Norman



"Florence" <[email protected]> wrote in message
news:[email protected]...
> 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

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


"Norman Jones" wrote:

> 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.
>
> ---
> Regards,
> Norman
>
>
>
> "Florence" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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

> 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.
>
> ---
> Regards,
> Norman
>
>
>
> "Florence" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

Hi Florence,

See the ProtectSharing method in VBA help.

---
Regards,
Norman



"Florence" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> "Norman Jones" wrote:
>
>> 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.
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Florence" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>>
>>
>>

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

> Hi Florence,
>
> See the ProtectSharing method in VBA help.
>
> ---
> Regards,
> Norman
>
>
>
> "Florence" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> > "Norman Jones" wrote:
> >
> >> 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.
> >>
> >> ---
> >> Regards,
> >> Norman
> >>
> >>
> >>
> >> "Florence" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >>
> >>
> >>
>
>
>

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.


---
Regards,
Norman



"Florence" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> Hi Florence,
>>
>> See the ProtectSharing method in VBA help.
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Florence" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >
>> >
>> > "Norman Jones" wrote:
>> >
>> >> 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.
>> >>
>> >> ---
>> >> Regards,
>> >> Norman
>> >>
>> >>
>> >>
>> >> "Florence" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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
>> >>
>> >>
>> >>
>>
>>
>>

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

> 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.
>
>
> ---
> Regards,
> Norman
>
>
>
> "Florence" <[email protected]> wrote in message
> news:[email protected]...
> > 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" wrote:
> >
> >> Hi Florence,
> >>
> >> See the ProtectSharing method in VBA help.
> >>
> >> ---
> >> Regards,
> >> Norman
> >>
> >>
> >>
> >> "Florence" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >> >
> >> >
> >> > "Norman Jones" wrote:
> >> >
> >> >> 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.
> >> >>
> >> >> ---
> >> >> Regards,
> >> >> Norman
> >> >>
> >> >>
> >> >>
> >> >> "Florence" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

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.


---
Regards,
Norman



"Florence" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> 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.
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Florence" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" wrote:
>> >
>> >> Hi Florence,
>> >>
>> >> See the ProtectSharing method in VBA help.
>> >>
>> >> ---
>> >> Regards,
>> >> Norman
>> >>
>> >>
>> >>
>> >> "Florence" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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
>> >> >
>> >> >
>> >> > "Norman Jones" wrote:
>> >> >
>> >> >> 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.
>> >> >>
>> >> >> ---
>> >> >> Regards,
>> >> >> Norman
>> >> >>
>> >> >>
>> >> >>
>> >> >> "Florence" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> > 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
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>