VBA on protected worksheet

[Gtasios4]

Hi all,

In order my VBA code to run in protected mode I have the below VBA codes:

Sub Edit_Sheet1()
'Unprotect Sheet1
Worksheets("Sheet1").Unprotect


'Do Something to Sheet1


'Reprotect Sheet1
Worksheets("Sheet1").protect
End Sub

As well as in the workbook:

Private Sub workbook_open()
    Dim ws As Worksheet
 
    For Each ws In ThisWorkbook.Worksheets
        ws.protect UserInterfaceOnly:=True
    Next ws
End Sub

However, when I right click in the tab of Sheet1 and protect manually with a password, then the VBA code needs also that code..

I want in the above codes to add a password, hence it could automatically and in the background the excel to unprotect itself and reprotect and so the VBA to work..

How should I change them?

Thanks in advance

 

[Joe4]

This link shows you the structure of the code with and without passwords:

VBA Protect / Unprotect Worksheets

This tutorial will teach you everything about Excel Worksheet protection in VBA - How to Protect or Unprotect Worksheets in Excel. Unprotect Excel

www.automateexcel.com

 

[Gtasios4]

This link shows you the structure of the code with and without passwords:

VBA Protect / Unprotect Worksheets

This tutorial will teach you everything about Excel Worksheet protection in VBA - How to Protect or Unprotect Worksheets in Excel. Unprotect Excel

www.automateexcel.com

Thanks for the reply Joe4,

I've advised that link to automate my excel. However, when a user clicks on my workbook he enters as read-only.

So, how can I alter the codes in order not to ask from the user to insert a password but running in the background?

If I type a password like 1234 in the below:

Should I also alter the codes such as below in order not to ask from the user to use a password?

Sub Edit_Sheet1()
'Unprotect Sheet1
Worksheets("Sheet1").Unprotect "1234"


'Do Something to Sheet1


'Reprotect Sheet1
Worksheets("Sheet1").protect "1234"
End Sub

 

[Joe4]

I am not sure I understand what you are asking here.
If the workbook is password protected, in order to unprotect it, the password must be entered (either manually or by VBA code).

If you want it to happen automatically, then it needs to be placed in one of the event procedures in Excel (like Workbook_Open, etc).
But you would need to define on what action and under what conditions it should be automatically unprotected.

 

[Gtasios4]

I am not sure I understand what you are asking here.
If the workbook is password protected, in order to unprotect it, the password must be entered (either manually or by VBA code).

If you want it to happen automatically, then it needs to be placed in one of the event procedures in Excel (like Workbook_Open, etc).
But you would need to define on what action and under what conditions it should be automatically unprotected.

I am not sure I understand what you are asking here.
If the workbook is password protected, in order to unprotect it, the password must be entered (either manually or by VBA code).

If you want it to happen automatically, then it needs to be placed in one of the event procedures in Excel (like Workbook_Open, etc).
But you would need to define on what action and under what conditions it should be automatically unprotected.

I am not sure I understand what you are asking here.
If the workbook is password protected, in order to unprotect it, the password must be entered (either manually or by VBA code).

If you want it to happen automatically, then it needs to be placed in one of the event procedures in Excel (like Workbook_Open, etc).
But you would need to define on what action and under what conditions it should be automatically unprotected.

Yes I know in order to unprotect a sheet the user should enter the password. The problem though is on the fact that when the user opens the workbook in read only mode it asks him to enter a password to unprotect the sheet.

Why is that happening?

 

[Joe4]

Because you have this code here:

Private Sub workbook_open()
    Dim ws As Worksheet
 
    For Each ws In ThisWorkbook.Worksheets
        ws.protect UserInterfaceOnly:=True
    Next ws
End Sub

Workbook_Open code fires automatically whenever the workbook is opened.
Since the code is trying to unprotect the workbook, but doesn't use the password, it is going to prompt the user for one.

So, you have two options:
1. If you do not want it to try to unprotect all sheets automatically upon opening (which, I don't know why you would - what would be the point of the protection?), then get rid of this code.
2. If you want it to automatically unprotect all the sheets upon opening, then add the password to the "Unprotect" line of that code.

 

[Gtasios4]

Because you have this code here:

Private Sub workbook_open()
    Dim ws As Worksheet
 
    For Each ws In ThisWorkbook.Worksheets
        ws.protect UserInterfaceOnly:=True
    Next ws
End Sub

Workbook_Open code fires automatically whenever the workbook is opened.
Since the code is trying to unprotect the workbook, but doesn't use the password, it is going to prompt the user for one.

So, you have two options:
1. If you do not want it to try to unprotect all sheets automatically upon opening (which, I don't know why you would - what would be the point of the protection?), then get rid of this code.
2. If you want it to automatically unprotect all the sheets upon opening, then add the password to the "Unprotect" line of that code.

Thank you for your feedback Joe4!

I don’t understand that much your 2. point. What do you mean exactly? How the code should be altered?

 

[Joe4]

Like this:

Private Sub Workbook_Open()
    Dim ws As Worksheet
 
    For Each ws In ThisWorkbook.Worksheets
        ws.Protect UserInterfaceOnly:=True, Password:="1234"
    Next ws
End Sub

 

[Gtasios4]

Like this:

Private Sub Workbook_Open()
    Dim ws As Worksheet
 
    For Each ws In ThisWorkbook.Worksheets
        ws.Protect UserInterfaceOnly:=True, Password:="1234"
    Next ws
End Sub

Thank you so much for your help!

 

[Joe4]

You are welcome.