VBA to protect and unprotect workbook and columns

Please Help – new to writing codes!!

I need 3 codes for a Leave Planner on a shared drive; the workbook is read only and password to modify and the sheets are also protected with access to filters only.

1 On_close and on_save I need to hide columns ‘D to J” across sheets 2,3,4,5, return to first sheet and protect workbook. (So confidential details are always hidden from read only users).


2 On_open I need a password prompt to unprotect workbook and unhide columns ‘D to J” across sheets 2,3,4,5 so I will not have to go to tools, protection, unprotect - select rows and unhide each sheet every time I need to add data.


3 And finally I have a password code on the summary sheet that reveals the password when typed! Is there a line I can add to the code below that will **** it out? (It’s protecting pivot tables; I needed a workaround to avoid the warning ‘cant update pivot table when protected’)

Please Login or Register  to view this content.

At this point I should probably also add that the VBA will be view and edit protected when complete.
Thanks heaps in advance!!!! – any other suggestions are also welcome

magikmel;

Suggestion 1. Indent you code, so it's easier to follow, like this:

Please Login or Register  to view this content.

It's a lot easier to see that you have 'If P = "test"' twice, and that the 2nd one will never occur.

1 On_close and on_save I need to hide columns ‘D to J” across sheets 2,3,4,5, return to first sheet and protect workbook. (So confidential details are always hidden from read only users).

In menus goto Tools->Options->Security to password protect the Workbook
These won't solve all your problems, but they will get you started

Please Login or Register  to view this content.

2 On_open I need a password prompt to unprotect workbook and unhide columns ‘D to J” across sheets 2,3,4,5 so I will not have to go to tools, protection, unprotect - select rows and unhide each sheet every time I need to add data.

Please Login or Register  to view this content.

Everyone will have to enter a passwork to open the workbook, and then it will also ask everyone to enter a 2nd password to unhide the columns. If you don't want it to ask every one the 2nd password, you can put an empty file on your local computer and have Workbook_Open() look for that file, and when it finds it, then just automatically unhide the columns, and leave them hidden for anyone who doesn't have that file on their local computer..

3 And finally I have a password code on the summary sheet that reveals the password when typed! Is there a line I can add to the code below that will **** it out? (It’s protecting pivot tables; I needed a workaround to avoid the warning ‘cant update pivot table when protected’)

Don't know of anyway to hide what is typed into an input box. But you could try telling them to type the password code into a cell (A25000), and format the cell's .Interior.ColorIndex and .Font.ColorIndex the same color so the text is invisible and use this code

Please Login or Register  to view this content.

The code seems to be bugging out on one line, and Im not sure why (still learning code) . passwords are 'test'

I'm pretty sure it's because the worksheet is protected. At least when I ran the code, it crashed on the line:

Please Login or Register  to view this content.

So replace that 1 line with these lines:

Please Login or Register  to view this content.

Originally Posted by magikmel

I tried the code..still not working

That will teach me not to test my macros:

Please Login or Register  to view this content.

Originally Posted by magikmel

I really hate to say it..but this one isent working either.

These work on my computer. The only thing I can think of is that you're using the wrong password. The passwords are case sensitive.
Try these. I put in a simple error trap that will tell you if that is the problem.
Change the word "test" in the 1st 2 lines to make sure they are correct.
myWBPassword is the password for the workbook
mySHPassword is the password for each worksheet
Edit: I just thought of something. When you open the workbook, you will get past the first password. If it crashes it's because you have the wrong password for the sheet.

Please Login or Register  to view this content.

If they still crash make a note of the err # & description. I'll look at it tomorrow. Getting late for me.

Ok good new and bad news

I pasted you last code with the error traps and made only one change to the workbook open code from true to false..IT WORKS!!!!!...but im still getting the error # 0 - what could be the cause if its working as it should? Oh and another minor thing the page shakes quite a bit when the codes are activated.
Im thinking the error might be that the workbook is not unprotecting on open - columns are hiding and unhiding though

Magikmel

I'm assuming the error# 0- means there is *no error* (am i right??) so I removed the trap. New code below

Yes Error# 0 means no error.
My bad, I shouldn't work so late at night. I didn't notice that the trap was being executed even when there was no error.
The line

Please Login or Register  to view this content.

should have been:

Please Login or Register  to view this content.

I also removed .Protect mySHPassword from the workbook open code and now it works great!!.

This is leaving the worksheets unprotected, which means that anyone can unhide the columns you want hidden.