New MS Excel User with question regarding password protecting tab

jtdinh205

New Member
Joined
Mar 7, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello All,

I am new to using MS Excel and have been tasked with a project to create a tab in a shared spreadsheet. I would like to make the specific tab protected so that it is only viewable by invitees or password protected, but if password protected, I would like somehow to make it auto-protected again if a user unlocks it with a password and forgets to lock it again. Thank you in advance.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

jtdinh205

New Member
Joined
Mar 7, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
What to write exactly for the macro? Also, the second link didn't work. Thank you.
 

rollis13

Active Member
Joined
Jul 30, 2012
Messages
426
Office Version
  1. 2016
Platform
  1. Windows
Don't you google the web ?:oops:

I will now show you two different approches but when you decide which suits your necessity both events will have to be the same.
In the first example all sheets will be locked while in the second only selected sheets will be locked, your choice.
If no password is needed just omit the parameter.
These two macros go in vba module "ThisWorkbook".
VBA Code:
Option Explicit
'example 1:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim ws     As Worksheet
    For Each ws In ActiveWorkbook.Worksheets      '<= for every sheet in workbook
        ws.Protect Password:="yourpasswordhere"   '<= change if needed
    Next ws
End Sub
'example 2:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim myShts, sht
    myShts = Array("Sheet1", "Sheet2", "Sheet3")  '<= change as needed (rename, add or delete sheet names)
    For Each sht In myShts
        With Sheets(sht)
            .Protect Password:="yourpasswordhere" '<= change if needed
        End With
    Next sht
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,791
Messages
5,638,340
Members
417,021
Latest member
moon miner

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