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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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