Lock AND Protect all work sheets VBA

Hi i keep getting error 1004 when i try this, however it works sometimes and is very random, does exactly what i require but then sometimes the error comes up??

Would be grateful if anyone can offer any help

Here is the code anyway

Please Login or Register  to view this content.

Your workbook may not be active when you are running that code.

Hi thanks for the responses, Sinhus - this macro works perfectly with different code like just locking all the sheets. Should this code not go through each worksheet??

Hi Legend Rubber, unfortuantely that wouldnt work as this macro needs to be used across loads of files and all tabs will have different names.

What i am essentially looking for is a macro to lock everything so its like a hard copy for audit purposes for example. Nobody could potentially edit it

okay, what about

Please Login or Register  to view this content.

?

Looping is fine. Only thing is your workbook must be active, before you execute the code For each sheet in Activeworkbook. If none of the workbook is active at the time of execution, it will throw error. That is why you are getting that error randomly.

Originally Posted by rduggy

Hi thanks for the responses, Sinhus - this macro works perfectly with different code like just locking all the sheets. Should this code not go through each worksheet??

Hi Legend Rubber, unfortuantely that wouldnt work as this macro needs to be used across loads of files and all tabs will have different names.

What i am essentially looking for is a macro to lock everything so its like a hard copy for audit purposes for example. Nobody could potentially edit it

Sindhus,
just for my knowledge, would the ((workbooks("yourworkbookname").activate )) code that i suggested work to avoid this error?
or is there a better way?

Hi -

Is the line

Please Login or Register  to view this content.

get highlighted after the error? if so, then your workbook has a hidden sheets which causing the error.

Regards,
Event

I think this is only part of the code. OP said in the previous post that this code will be executed for loads of files. so it may not be desirable to manually type the file name everytime.

@rduggy How are you executing this code for loads of files? Are you using separate code for that?

Originally Posted by Legend Rubber

Sindhus,
just for my knowledge, would the ((workbooks("yourworkbookname").activate )) code that i suggested work to avoid this error?
or is there a better way?

Hi everyone, i tried legend Rubber suggestion of

Please Login or Register  to view this content.

But no luck

@event21 yeah that is exactly the issue except i have no hidden tabs and the same error pops up even if i open a new excel file and add the macro.

Would be very grateful if someone had a solution.

Has anyone tried this themselves??

why dont you post a workbook with the code so we can play around with it...
Sorry that my suggestion didnt work... we will try again

Try this one. I got an error when protecting sheet2. Cells.select was pointing to sheet1 only. So i selected sheet2 before executing cells.select

Please Login or Register  to view this content.

Hi Sindhus didnt see you post till now, just tested and it seems to have worked

Makes sense too lol

Thanks again