Protected Sheet Will Not Allow Macro To Run

[NaNaBoo]

I have protected several sheets in my workbook to protect the forumlas and data for being tempered with by the users.

When I try to run my macro, I get an error message stating:

Runtime '1004'
You can not use this command on a protected sheet

And the debug is pointing to this part of my code

Please Login or Register  to view this content.

How can I revise my code to allow me to have my sheets stay protected and still be able to run my macro?

[modytrane]

use following at the top of your code to unprotect the sheet and protect it at the end.

modytrane

[bitterenemy]

(I previewed my post and noticed that there was already a response. Maybe someone could provide some input the last section.)

This is how I've solved this in the past:

Please Login or Register  to view this content.

One problem I had is I wanted to keep the password from the people viewing the file, and if you press Alt-F11, VBA pops up showing this code, and therefore the password. I don't know if there is a way around this or if it matters to you either way.

[BigBas]

^ You have the option of locking/protecting the VBA project. That way, an end user cannot see the code at all, and therefore can't see the password. If the end user does need access to the VBA, your options become more limited.

[bitterenemy]

How do you protect the VBA code? I would really like to do this on the file i was working on.

Thanks!

[BigBas]

In the VBE, go to TOOLS-VBAProject Properties. Choose the Protection tab, lock the project, then choose your password.

[NaNaBoo]

THanks,

I see that I have been given a couple of ways of handling this problem. Let me address the first suggestion and make sure I am understanding this right. In this code;

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Within the macro that needs the sheets to be unprotected, I would include this code at the top and at the bottom?

I will not include this in the individual protected sheets?

Please advise.

[NaNaBoo]

I am thinking that I can also try to solve my problem another way.

If I physically have to unprotect each sheet when I open up the Excel spreadsheet, can I have a code that will "Protect" the sheets after I exit out of Excel?

If so, what code would I need to accomplish this?

[BigBas]

NaNa:

I noticed that earlier you had another thread that pertained to timing, so to speak. It is important to note that VBA acts in the order of the commands. So, if you need to unprotect the sheet in order to code something, you want to add the unprotect code before your current code. Then you can reprotect afterwords. I will write up a short bit of code that colors the active cell as an example.

Please Login or Register  to view this content.

[bitterenemy]

If you want to ensure that your sheet is protected every time you close your workbook, you could but your protect code in the BeforeClose event.

Please Login or Register  to view this content.

[NaNaBoo]

BigBas,

By studying you code, I see what you mean. You code is a simply command. But I am complex as to how to get that to work in my workbook.

See, I have about 6 sheets which are protected.

Then I have a one master sheet that will need to consolidate the data from the 6 sheets which are protected. This master sheet will run Macro1().

Now, Do I need to include this code on each of the 6 sheets?

Please Login or Register  to view this content.

How will the Macro1() work?

[NaNaBoo]

Bitterenemy,

Thanks. Will I need to include the Passwords as you and BigBas as shown me in your examples?

Here is my idea of how I will fit this in my code. Let me know if this is right.

Please Login or Register  to view this content.

[bitterenemy]

What you've done looks good to me. I don't entirely follow your body of code, but I see that you're accessing each sheet and modifying data. You unprotect the sheet, then your macro is free to run, then your protect it again at the end.

[NaNaBoo]

Thanks again BigBas and BitterEnemy,

The code is working the way it should. But I do have another question, that has nothing to do with this but does.

Should I post it here or start a new thread?

[modytrane]

Start a new thread.
Also, another suggestion.
It seems that you had a couple of issues.
1. You wanted to unprotect and protect sheets with the macros.
2. You wanted to know how to protect sheets at the end when you close the workbook.

It usually works best for you if you post a sample workbook and explain what you are trying to do.

You could have gotten all your answers in less than three messages instead of 14.

modytrane.

[NaNaBoo]

Thanks. I will do that for my next project.
You guys are awesome