0

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?

CASE I

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?

[DONE, THANK YOU.] CASE II

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

THANK YOU VERY MUCH.

Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
user1543250
  • 173
  • 2
  • 2
  • 9
  • 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. – html_programmer 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. – ApplePie Jul 22 '12 at 13:25

1 Answers1

-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.

ApplePie
  • 8,814
  • 5
  • 39
  • 60
  • Have you checked out [the question of which this question is a duplicate](http://stackoverflow.com/questions/125449/how-to-protect-cells-in-excel-but-allow-these-to-be-modified-by-vba-script)? – 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. – ApplePie 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. – ApplePie Jul 22 '12 at 18:02