Take the 2-minute tour ×

Possible Duplicate:
How to protect cells in Excel but allow these to be modified by VBA script

any chance to run a specific marco [GodMarco] before any marco [UserMarco] which fired by user?


I have a worksheet, which have lots button to do different action by user.

I have normal cells to let user input data, and have hidden cells to do the dirty work.

And since user may user [arrow] or [tab] to circle through, I have to protect the sheet.

And made them not selectable.

And now VBA stopped.

I know I can add vba codes to unprotect and protect again and again, but I think it is stupid to add them to each marco.

So can it be something like GODMODE that before each marco fired by user, run GODMARCO1, and after user marco, run GODMARCO2?


Or can we get some cells not selectable by user but selectable by VBA?


share|improve this question

marked as duplicate by GSerg, George Stocker Jul 23 '12 at 16:44


Who is Marco? :-) –  Kim Gysen Jul 22 '12 at 11:17
Why is it stupid to call a function to protect / unprotect if that is the requirement? I think that many do it. On the other hand, Are you sure that you need cells for dirty work? Not that there's something wrong with it necessarily. –  Kim Gysen Jul 22 '12 at 11:21
@KimGysen: While I agree with you that if protecting/unprotecting is a requirement, doing it is not "stupid" (and we do on some spreadsheets at work), I however don't think that having a few markers here in there, invisible to the client, is a sign that something is wrong. Sometimes there is no other way. –  Alexandre P. Levasseur Jul 22 '12 at 13:25
OP: see my answer. –  Alexandre P. Levasseur Jul 22 '12 at 17:19

1 Answer 1

Open the VBA window (Alt + F11). In the property window of the sheet you want to protect you will see the "ScrollArea" property which will limit the area that can be selected by the user, set it to whatever range you want to make accessible to your users.

share|improve this answer
Have you checked out the question of which this question is a duplicate? –  GSerg Jul 22 '12 at 15:32
Yes. What I propose has the same effect + you can set it so the user can actually modify some cells, just not all. This is closer to what OP asked than what you linked to. –  Alexandre P. Levasseur Jul 22 '12 at 17:18
This is very far from what OP wants. Nobody uses ScrollArea for that because it serves a different purpose. Try to make each odd cell in third row and each even cell in fourth row editable, and all other cells protected from editing, only using ScrollArea. Each cell in Excel has Locked property which is True by default. It is represented with a checkbox in the interface. You allow some cells to be editable by clearing this checkbox and protecting the sheet. Only cells with the cleared checkbox will be editable. This is what OP is doing, and it is the correct way. –  GSerg Jul 22 '12 at 17:27
"And made them not selectable." This sounds like he wants to make cells unselectable. –  Alexandre P. Levasseur Jul 22 '12 at 18:02
