VBA to Unprotect/Protect Worksheet - for a shared workbook

[surkdidat]

I am not sure what I need is possible, but worth asking!

I have shared workbook that is also has the sheet protected (no password)

I have an existing macro that to run, needs to unprotect the worksheet, run, then protect again.

When not shared it works fine, but cannot run when shared (due to the unprotect/protect situation)

Any ideas, gratefully received!

 

[Kamolga]

I am not sure what I need is possible, but worth asking!

I have shared workbook that is also has the sheet protected (no password)

I have an existing macro that to run, needs to unprotect the worksheet, run, then protect again.

When not shared it works fine, but cannot run when shared (due to the unprotect/protect situation)

Any ideas, gratefully received!

Unprotect the sheets manually and get them protected using

[COLOR=#666666][FONT=Lora]Sheets(“[/FONT][/COLOR][COLOR=#0000cd][FONT=Lora]sheetname[/FONT][/COLOR][COLOR=#666666][FONT=Lora]”).Protect UserInterFaceOnly:=True[/FONT][/COLOR]

so the sheet is protected for users but not macro's

I personnaly launch the macro automatically when opening workbook so don't neet to protect sheets everytime I unprotect them

Private Sub Workbook_Open()
Dim wSheetName As Worksheet
For Each wSheetName In Worksheets
wSheetName.Protect Password:="Secret", UserInterFaceOnly:=True
Next wSheetname 
[COLOR=#666666][FONT=Lora]End Sub[/FONT][/COLOR]

 

[surkdidat]

Apologies - slightly confused. Say, for example, I want column D protected at all times, but need the workbook shared. How do I put the line in the code, (on opening of workbook in Sheet1) so that I can run my macro, without having to unproect column D please?

Unprotect the sheets manually and get them protected using

[COLOR=#666666][FONT=Lora]Sheets(“[/FONT][/COLOR][COLOR=#0000cd][FONT=Lora]sheetname[/FONT][/COLOR][COLOR=#666666][FONT=Lora]”).Protect UserInterFaceOnly:=True[/FONT][/COLOR]

so the sheet is protected for users but not macro's

I personnaly launch the macro automatically when opening workbook so don't neet to protect sheets everytime I unprotect them

Private Sub Workbook_Open()
Dim wSheetName As Worksheet
For Each wSheetName In Worksheets
wSheetName.Protect Password:="Secret", UserInterFaceOnly:=True
Next wSheetname 
[COLOR=#666666][FONT=Lora]End Sub[/FONT][/COLOR]

 

[Kamolga]

Apologies - slightly confused. Say, for example, I want column D protected at all times, but need the workbook shared. How do I put the line in the code, (on opening of workbook in Sheet1) so that I can run my macro, without having to unproect column D please?

Hi,

Unprotect the sheet manually. Then go in VBA, 2x click ThisWorkbook (under the sheets names in left column) and paste

Private Sub Workbook_Open()
worksheets("Sheet1").protect UserInterFaceOnly:=True
[COLOR=#666666][FONT=Lora][I]End Sub[/I][/FONT][/COLOR]

Save the workbook and close it.

When you open it, Sheet1 is protected for you but not your macros

 

[surkdidat]

I have put this code in, and shared the workbook, saved and closed and re-opened.

The cells in column Q are protected, but I can still overwrite the content....

Hi,

Unprotect the sheet manually. Then go in VBA, 2x click ThisWorkbook (under the sheets names in left column) and paste

Private Sub Workbook_Open()
worksheets("Sheet1").protect UserInterFaceOnly:=True
[COLOR=#666666][FONT=Lora][I]End Sub[/I][/FONT][/COLOR]

Save the workbook and close it.

When you open it, Sheet1 is protected for you but not your macros

 

[Kamolga]

I just tried it and it works as such if your sheet is called Sheet1.


You have to put it in ThisWorkbook, not a module

 

[surkdidat]

Works in a new workbook fine, will investigate as to why it did not want to play in my proper spreadsheet now! Thank you!

I just tried it and it works as such if your sheet is called Sheet1.


You have to put it in ThisWorkbook, not a module

 

[surkdidat]

I have got past that issue now (thank you) - the next one that happens is that I get an error message of "Unable to set the AutoFilterMode property of the Worksheet class when trying to run the Macro...... Any ideas please?

I just tried it and it works as such if your sheet is called Sheet1.


You have to put it in ThisWorkbook, not a module

 

[Kamolga]

The very first time in a very new workbook it does not due to security default setup, need to enable editing once then it works. I would check macro security in devloper tab.

Anoter option is your Events are turned off but I don't thing Excel saves the status when it closes...to be ckecked: In the immediate window type

[COLOR=#333333]Sub test()[/COLOR]
[COLOR=#333333]Application.enableevents = true
[/COLOR][COLOR=#333333]End Sub[/COLOR]

in a module, run it, save and close Excel.

The unlikely tird option is an old "cut and paste event" issue; if you copied and pasted "Private Sub Workbook_Open()" line, then Excel can sometimes fail. To link that with the actual event, pick the event from the dropdowns (workbook on the left, open on the right) so it creates a new event and try it again.

 

[Kamolga]

I have got past that issue now (thank you) - the next one that happens is that I get an error message of "Unable to set the AutoFilterMode property of the Worksheet class when trying to run the Macro...... Any ideas please?

Did not read this before previous post.

If you unprotect the sheet and run te macro, do you ave the same error?