Protect a range in a newly created excel file

Hi burugudug;
Here's from the help file:

Protecting elements from all users You can prevent users from inserting, deleting, and formatting rows and columns, from changing the contents of locked cells, and from moving the cursor to cells that are locked or to cells that are unlocked.

By default all cells on a worksheet are locked. Before you protect a worksheet, you can unlock cells where you want users to enter and change data, in two ways. To unlock cells for all users, you can use the Protection tab of the Format Cells dialog box. To unlock cells for specific users, you can use the Allow Users to Edit Ranges dialog box. Any ranges that you specify in this dialog box and don't assign a password for are also unlocked for all users. The cells you leave locked become protected only after you protect the worksheet.

see TOOLS>PROTECTION>ALLOW USER TO EDIT RANGES

Hi burugudug;

Easiest way is to record a macro doing exactly what you want, then run the macro from Sub Workbook_Open()

Hi burugudug;

Yes you can modify it. I take it you have not written any macros before.
After you record the macro, you will probably want to modify it. Recorded macros are almost always too specific. they record every cell's address that you use, but you will want to change it so that you can use it for other cells.

After you record the macro, post it here on the forum, and we can tell you how to change it to do exactly what you want. To find the macro after recording it, click Alt+F11 to open Visual Basic Editor. If it's not immediately visible (it will be named "Sut Macro1" (or maybe a higher #)), click Ctrl+R to open the Project Explorer, and look through Module1, Module2, etc. until you find the macro. Then cut & pasted it into a message here on the Forum (and be sure and put code tags around the code, or the moderators might get angry).

Hi burugudug;

why is the creation of password not included in the macro?

That's a good question. It makes sense not to store the password any place that someone can find it.

Warning: putting passwords on things can get tricky. And it is possible that you might accidentally lock yourself out, so back up the file before you start. I recently was trying to help someone with passwords and it took us a while to figure out that he had accidentally changed the password to something else and he couldn't get back in.

To allow people to edit some cells you have to unlock then. Assuming that you want the users to be able to work in all cells except columns B & E
Just type (or cut and paste) this into the same window where Macro1() is stored. Obviously change myPassword to your password (be sure to surround it with "s)
Only put in one of the "sh.EnableSelection" lines. If you want to allow users to select the locked cells but not change them use xlNoRestrictions. If you don't even want them to be able to select them use xlUnlockedCells.

Please Login or Register  to view this content.

Now you want to run this macro at the appropriate time.
Do you want it run it every time the workbook opens? Maybe every time anyone activates the sheet? Or maybe only when you tell it to (and you just save the workbook with the sheet protected, that way it's protected the next time the workbook is opened).

I'm kind of guessing that having it protected every time the workbook opens would work. When you want to unprotect it you can, but when someone else opens the workbook it gets protected. That way if you forget to protect it when you close it, it still gets protected the next time it's opened.

Instructions for protecting the sheet every time the workbook is opened.
First take note of the sheet name that you want to protect (I'm guessing that it is Sheet1). While the window with Macro1() is open, click Ctrl+R (hold the Ctrl Key down while clicking "R"). This will open the Project Explorer. You should see a line highlighted that says "Module1" (maybe some other number). It should be the same as the title of the window with Macro1() in it. Above that line you will see a line "ThisWorkbook". Double Click on it. This should open another window like the one with Macro1(), but it will say "ThisWorkbook" at the top. In that window type (or cut & paste) this code:
In a line above the "ThisWorkbook" you will see a list of all the sheets in the workbook. The sheet name will be in "()"s. The name in the "()" is the name you want to type into this macro in place of "Sheet1"

Please Login or Register  to view this content.

The other consideration; can any body else look at your macros? Remember your password to protect the sheet is stored in Protect_Sheet(). All anybody has to do is click Alt+F11 to see the macros. If so, you will want to password protect them also. To protect your macros: In the menus TOOLS>VBAProject Properties>Protection. Click "Lock Project for Viewing" and enter your password (it can be the same password that you use to protect the sheet, but doesn't have to be).

Hi burugudug;

We have some confusion here. When you create a new workbook, you will have to copy the macros over to the new workbook. It's a lot harder to write a macro that will put the macros into a new workbook for you. Unless you plan on creating A LOT of new workbooks, I don't plan on writing a macro that puts the other macros into the new workbook. It's a lot easier for you to copy them into the new workbook.
Let me know if the instructions I gave you to put these macros into your workbook aren't good enough for you to copy them into a new workbook.

Please Login or Register  to view this content.

This code won't work. To protect a range, you need to use "AllowEditRanges", but they are a lot harder to work with, than just locking the range. You still have to protect the sheet. If you protect a range it will ask you for a password every time you try to edit it. But if you lock the range, it just doesn't let you edit it. To edit a locked range, you just unprotect the sheet once and you can edit it until you protect the sheet again.

Please Login or Register  to view this content.

To run this sub, you have to pass it the worksheet so that it knows what worksheet to protect (that's what the "sh As Worksheet" is). It's just putting the worksheet into a variable named sh.
This sub does this to the worksheet that is passed to it:
1) Unlocks all the cells on sh
2) Locks Columns B & E
3) Protects the worksheet, assigning a password to it.
4 or 5) Tells Excel what cells to let the user move the cursor into (just the unlocked cells or all cells), but they can't change any locked cells (Columns B & E).

[QUOTE=burugudug;2343955]

Originally Posted by burugudug

does this code

Please Login or Register  to view this content.

automatically put the code in the worksheets?
QUOTE]

foxyguy,
sorry my mistake, what i mean by the statement quoted above is, will the password work in the newly created workbook?

I can't see how you are using it. As long as you use it the way I set it up then yes it will work.

Your question makes me think that you don't understand something. I'm not sure what you don't understand, so I will explain everything.

You have a macro named Protect_Sheet. It will protect any sheet that you tell it to protect. It allows all cells to be edited except columns B & E. It will attach a password (the word between the "s) to the sheet. All you have to do is tell it what sheet you want to protect. You can tell it to protect every sheet, if you want, by passing it every sheet in the workbook one at a time.
This macro is located in a Standard Module (named Module1 or similar). This macro runs ONLY when it is called by another macro.

Please Login or Register  to view this content.

You also have a macro in ThisWorkbook module. This macro calls the macro named Protect_Sheet and passes it the sheet you want to protect. It automatically runs every time the workbook is opened. This macro will only work if it is located in the ThisWorkbook module.

Please Login or Register  to view this content.

Both of these macros work in the workbook that they are located in. If you want a different workbook to do the same thing, you have to copy the macros into the new workbook, into the same modules.

Originally Posted by burugudug

if its not possible so i guess ill do the protecting of columns manually after creating the new workbook then

If it's a one time event of protecting the columns, then it's probably easier to do it manually.

If it's something that needs to be done a lot because there is someone who has to edit those columns, but needs it protected against every body else then you might want the macros to protect the columns every time the workbook is opened, so when that person forgets to re-protect then, it does it automatically.

I have thought about it a little more, and it wouldn't be very hard to put specific code into another workbook automatically. I could probably write it in less than an hour. If it's something you are going to need more than a few times, I could write it for you.

One last option is to create a template that has the code in it. Then when you create a new workbook, you just tell Excel to use the template and they will be there waiting for you.