Small amendment needed in code

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
1,069
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Did I miss something ....:confused:

Did you self solve ???
 
Upvote 0
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:
Upvote 0
Looks like I was not overestimating your ability after all ;)
Glad you got it sorted.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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