Small amendment needed in code

Ironman

Board Regular
Joined
Jan 31, 2004
Messages
186
Hi

The below runs with a Workbook Open event to allow VBA to modify a protected sheet without unprotecting the sheet:

Code:
Private Sub Workbook_Open()

 With ThisWorkbook
   Call UnprotectWorksheet(wks:=.Sheets("Training Log"))
   Call ProtectWorksheet(wks:=.Sheets("Training Log"))
 End With
I would be very grateful if you could amend this code so that another sheet (named "Daily Tracking") can also be temporarily unprotected.

Many thanks!
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,695
Office Version
  1. 365
Platform
  1. Windows
I am guessing that the obvious amendment did not work for you
So please post the 2 procedures below so that we can see what is going on
UnprotectWorksheet
ProtectWorksheet
 

Ironman

Board Regular
Joined
Jan 31, 2004
Messages
186
Thanks for replying Yongle.

You're overestimating my ability as I have virtually no knowledge of VBA so I haven't made any amendments. I'm sure the obvious will work.

Thanks
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,695
Office Version
  1. 365
Platform
  1. Windows
I still want to see them
 

Ironman

Board Regular
Joined
Jan 31, 2004
Messages
186

ADVERTISEMENT

Code:
 With ThisWorkbook
   Call UnprotectWorksheet(wks:=.Sheets("Training Log"))
   Call UnprotectWorksheet(wks:=.Sheets("Daily Tracking"))
   Call ProtectWorksheet(wks:=.Sheets("Training Log"))
   Call ProtectWorksheet(wks:=.Sheets("Daily Tracking"))
 End With

Works a dream. Simples.

Thanks for your help.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,237
Office Version
  1. 2013
Platform
  1. Windows
Did I miss something ....:confused:

Did you self solve ???
 

Ironman

Board Regular
Joined
Jan 31, 2004
Messages
186
Hi Michael - yes I did. I tried combining the second sheet name with the first, separated by a comma and thought that would work, but it didn't. I then thought a bit longer about and came to the basic conclusion that maybe VBA isn't that clever so I just tried repeating the 2 rows with the second sheet name and it worked.

Not sure why that wasn't suggested; I did say I knew nothing about VBA so I impressed myself a bit there.

We both know that the lengthy code that runs when the workbook opens affects 2 worksheets - all the above code does is unprotect both sheets to let the code run in its entirety - posting the code would have been pointless.
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,695
Office Version
  1. 365
Platform
  1. Windows
Looks like I was not overestimating your ability after all ;)
Glad you got it sorted.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,507
Messages
5,523,309
Members
409,510
Latest member
HQ2401

This Week's Hot Topics

Top