Locking an entire worksheet via VBA

[andyb16]

Hi All,

I have a workbook into which I create new tabs for a new month. I want to be able to lock all the earlier tabs keeping only the last (new) tab unprotected with edits possible.

Lets say the password is "abc".

Now I am good at advanced excel with formulas but am just a novice when it comes to VBA.

Please could someone suggest me a code and walk me through it so that I learn along the way.

Thanks VM!

 

[Domski]

How do you create the new one? Do you copy an existing sheet?

Dom

 

[andyb16]

Hi Dom,

Yes, I use move or copy - create a copy option. So yes, bascically I do copy an existing sheet and just clear out last months numbers.

-Andy

 

[justme1122]

From one VBA novice to another using the macro recoreder I got:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

You could the put this in a loop so that it selects one sheet at a time and locks them. From my very limited know how it would be easyer if the new worksheet was the first one to that the code would start with "selecet worksheet #1 +1".

I would have to look up how to use loops and stuff again sorry,

 

[Domski]

Assuming only the most recent sheet is locked at any one time and that you would copy that sheet to be the new one:

Sub Test2()

Dim wsSourceSheet As Worksheet

Set wsSourceSheet = ActiveSheet

ActiveSheet.Copy After:=Sheets(Sheets.Count)

wsSourceSheet.Protect Password:="abc"

End Sub

If that's not the case let me know.

Dom

 

[andyb16]

Thanks Both.

Domski - first of all its working so thanks vm .

I have some questions just to increase my knowledge so that I can build code myself somewhere in the future:

1) Do in need to create a user form to make it easier for me to run the code. I mean where else can I run this control?

2) When you define wsSourceSheet As Worksheet, what are you achieving?

3)Also can you please explain what ActiveSheet.Copy After:=Sheets(Sheets.Count) does? esp the "(Sheets.Count)" part.

Thanks again and sorry for picking your brains so!

-Andy

 

[Domski]

1) There are various ways. You can attach the macro to a custom toolbar button, create a custom menu using vba or just run the macro by pressing Alt+F8 and choosing the one to run. I wouldn't bother with a userform.

2) By doing this I create a worksheet variable, this is then used to store the initial active sheet as it will no longer be the active one once I've copied it and created the new one and I can easily then go back and lock it.

3) That just copies the active sheet and tells it to locate the copy at the end after all the other sheets in the tab strip, hence the count of the existing sheets. If you'd prefer the new sheet to appear as the first one on the tab strip use: ActiveSheet.Copy Before:=Sheets(1)

Dom

 

[andyb16]

Got it. Thanks Dom!

 

[brianburen]

I have a workbook that has 15 sheets, and some of them are locked, but when I need to edit them had to goto each sheet and unlock.

Heres a better way:
I created a userform, that not only had the "lock button" on it but a textbox and "unlock button" so that i could enter a password and then click unlock.

Hope this helps.................