Excel 2007/2010 - Can I have workbook specific disabling of row inserting?

[frank_m]

There is a routine at Ron Debruin’s site that apparently will Hide most of but not all of the Ribbon and QAT, but leaves intact the office button with limited menu choices such as save, save as, close and print, I believe. This interests me a lot

With an .xlsm file I am planning to try to implement that, so that I can make it very difficult for anyone to use design mode or to insert rows, so that I can limit row insertions to those done via a macro button.

My question is, in Excel 2007 or 2010, are there workbook specific alterations that can be made to the right click menu? As I really dislike disabling and re-enabling menu items using the open and close events.

Also let me ask, will I need to also disable some keyboard functions to prevent row inserts?. I am guessing that if there is I should be ok using the onkey method for disabling those.

[Jan Karel Pieterse]

I'm afraid you'll have to use workbook_Activate and DeActivate events to handle disabling/enabling of the insert menu option.

Control+ inserts a row/column/range (depending on the selection).

Don't forget the row header rightclick menu and the column header right-click menu.

[frank_m]

Thanks for the input Jan,

After considering my options more since I first posted, it looks to me like I can just block the right click popup all together, by using the right click event (cancel = true). And in its place provide a custom built popup, that has a few essential selections performed by macros.
(do to affects on simultaneously open workbooks, I have grown to despise setting any individual items to enabled = false, then resetting using events)

Initially I was thinking that I could not use any events, because occasionally a user goes into design mode to insert a new row. But, after looking over various code at Ron Debruin’s site, I'm now thinking that I can hide the QAT, as well as items from the ribbon, thereby making the use of design mode out of the hands of a typical user. And freeing up the ability to use events.

Do you see any flaws in that approach?

[frank_m]

I am thinking that if I am successful with making customizations to the ribbon that will make getting into design extremely difficult for the average user, then I could have an edit button that unlocks the cells in the active cell row, but keeps the sheet in protection mode.

I tested this approach as apparently proving effective, by unlocking a row of cells,
then protecting the sheet and attempting an insert using cntrl+

It would seem to me that this approach would also make it so I don't have to disable the right click menu.

Anyone disagree, or agree?

Thanks

[Jan Karel Pieterse]

Sounds good to me, but I wonder why making it hard to go into design mode is needed once you've protected the sheet?

[frank_m]

Well, I'll try to make that answer as brief as I can. - I want both the design mode button and protection button, not easily obtainable, rather than just having the safeguard of a password. -- For example, if only the I.T. guy knows the password at the beginning, before long several other people will pry it out of him. - Where as, if only he, or I, or similarly trained person, would ever need to get under the hood, other users for the most part will not be interested in learning how to circumvent things that way.

And if I were to only make the protection button difficult to get to, but lets say I were to leave the design mode out there for all to see, that's bad, as any toying with that can result in events not firing at possibly timely moments.