|
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 |
Can I unprotect / protect a shared workbook?
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 |
Can I unprotect / protect a shared workbook?
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 |
Can I unprotect / protect a shared workbook?
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 |
Can I unprotect / protect a shared workbook?
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 |
Can I unprotect / protect a shared workbook?
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 |
Can I unprotect / protect a shared workbook?
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 |
Can I unprotect / protect a shared workbook?
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 |
Can I unprotect / protect a shared workbook?
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 |
| All times are GMT +1. The time now is 03:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com