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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
What to write exactly for the macro? Also, the second link didn't work. Thank you.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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
Back
Top