VBA on protected worksheet

Gtasios4

Board Regular
Joined
Apr 21, 2022
Messages
71
Office Version
  1. 2021
Platform
  1. Windows
Hi all,

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

VBA Code:
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:

VBA Code:
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,531
Office Version
  1. 365
Platform
  1. Windows
This link shows you the structure of the code with and without passwords:
 

Gtasios4

Board Regular
Joined
Apr 21, 2022
Messages
71
Office Version
  1. 2021
Platform
  1. Windows
This link shows you the structure of the code with and without passwords:
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:

1654782716770.png


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

VBA Code:
Sub Edit_Sheet1()
'Unprotect Sheet1
Worksheets("Sheet1").Unprotect "1234"


'Do Something to Sheet1


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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,531
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 21, 2022
Messages
71
Office Version
  1. 2021
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,531
Office Version
  1. 365
Platform
  1. Windows
Because you have this code here:
VBA Code:
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

Board Regular
Joined
Apr 21, 2022
Messages
71
Office Version
  1. 2021
Platform
  1. Windows
Because you have this code here:
VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,531
Office Version
  1. 365
Platform
  1. Windows
Like this:
Rich (BB code):
Private Sub Workbook_Open()
    Dim ws As Worksheet
 
    For Each ws In ThisWorkbook.Worksheets
        ws.Protect UserInterfaceOnly:=True, Password:="1234"
    Next ws
End Sub
 
Solution

Gtasios4

Board Regular
Joined
Apr 21, 2022
Messages
71
Office Version
  1. 2021
Platform
  1. Windows
Like this:
Rich (BB code):
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,531
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
:)
 

Forum statistics

Threads
1,176,325
Messages
5,902,520
Members
434,977
Latest member
Wayne_Wickson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top