Password for recorded unprotection macro

[Si902]

Hi All

I am developing a basic shift rota workbook, i need others to be able to freely view each sheet of the workbook, however i need it to be password protected against editing.

I have recorded a number of Macros, two of which are for Protecting / Unprotecting respectively.

However,

Firstly it does not recognise the password i set during the recording proceedure, you can simply click the 'Unprotect Sheet' option on the Review tab. i have seen some code in other posts adding in text similar to Password=: "1234" however i cant get it to work.

Secondly, if you activate the macro on another sheet, i.e other than in the sequence you recorded it you get the Run-time error '91': dialogue box appear, is there a way round this?

I am obviously a complete novice when it comes to VBA so any pointers in the right direction would be greatly appreciated.

I've added a copy of the Workbook for your info.

Thanks in anticipation

[Si902]

Dont know why file hasn't uploaded, i will sort!

[Mordred]

Check the file size. If the workbook is greater than 1000 KB you will have to zip it first and then upload the zipped copy.

[Si902]

The file is too large!

My code is as follows -

For protecting

Please Login or Register  to view this content.

And for Unprotecting

Please Login or Register  to view this content.

[Si902]

Thanks Mordred, it is, i didnt notice.

Here is the zip file

[Mordred]

The way I see it, your procedures are running in a linear fashion that would be dependant upon the first sheet. It may be better to use something like:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Let me know how it goes.

[Si902]

I will give it a go thanks.

Can you add in the keyboard shortcut to the code? and if so would it sit between the first and second line?

[Si902]

Have tried the above and doesn't appear to work?

Also tried to add a keyboard shortcut as in my recorded macro with no joy.

Thanks anyway Mordred.

[snb]

Save a copy of your workbook; make it a read-only file. No other protection seems necessary I guess:

Please Login or Register  to view this content.

[Si902]

snb, i never thought of it that way, maybe worth a try, i don't really understand your code though.

[Si902]

Bump no response!

[john55]

hi si902
the Mordred's code works very well!
as an option, try to record a macro, stop it and then copy/paste the code in it

Please Login or Register  to view this content.

and for protect do the same steps

Please Login or Register  to view this content.

or open yr workbook protected

Please Login or Register  to view this content.

[snb]

1. You know what a macro is
2. You know how to start a macro
3. You can use a macro to save the workbook that contains this macro as a copy: change the drive (E), path (\snb\) and filename (example.xls) as you please
ThisWorkbook.SavecopyAs "E:\snb\example.xls"
4. you can make this copy of the workbook a read-only file:
CreateObject("scripting.filesystemobject").getfile("E:\snb\example.xls").Attributes = 1
5. integrated into 1 macro:

Please Login or Register  to view this content.

6. Please do not bump too fast (see the forum rules); remember the forum is being 'staffed' by volunteers who have other activities besides sleeping as well.

[Si902]

Hi John55

Thanks for that,

The first two Macro's work very well, it must have been the way i was trying to add in the keyboard shortcut that was causing me problems!

The Open workbook protected macro didnt seem to work for me, it must be down to my company's security settings (it wont allow me to always enable macros, i always have to manually allow content when opening docs).

Is there a way of having the user enter a password on the unprotect macro in order to get access?

[Si902]

Hi snb

Please accept my apologies, i really didny mean to cause any offence by bumping, i do appreciate the work forum members do in their own time.

Thanks for the explanation, i understand now, i'll give it a try.

Thanks again

[Si902]

Have found a thread which solves my issue with password protection, url as follows -

http://www.excelforum.com/excel-prog...-password.html

Thanks to all who took the time to assist.

si902