Detect when a row is inserted (Excel 2K SR1)

Topic

New Reply

Hello,

I am trying to deter the users of a spreadsheet from inserting rows or columns, but I do not want to prevent them from adding them if they absolutely need them.

So what I would like to do is add a macro that detects when the user inserts a row and then display a warning message. I will need to capture all methods of adding rows to the worksheet.

Thanks,

Reply | Quote

Replies

As far as I know, there is no event which fires when a row or column is added to a worksheet. Therefore, it is going to be very difficult to do what you want. The only thing that I can think of to do would be to add a hidden row and column that contains sequential numbers. Then use one or more of the worksheet events (change, selection change, activate, deactivate, etc.) to check to see if there are any gaps between the numbers in your hidden row and column. This will not catch it exactly when it happens, but will catch it the next time one of those events fires. However, this will also cause a lot of overhead, and could slow things down quite a bit.

Another thing that you could do would be to protect the worksheet. That should disable the Insert Row/Column menu commands. Then write a macro to add the row/column when you want to allow the user to do so.

Reply | Quote

When I want to stop Users inserting rows I add a hidden column and enter an array alongside the rows I want ‘protected’. When the User tries to insert a row Excel displays a message “You cannot change part of an array”.
To do this,
1. highlight the row range required
2. with the range selected, type “=1” (without quotes) then
3. Press [Ctrl][Shift][Enter] to enter as an array

Ditto for columns if you don’t want columns inserted within a range.

To disable, simply unhide the aray column, select all of the array cells and hit the [Delete] key to remove the array.

OK it’s not perfect and some Users get confused with the Excel message.
The upside is they don’t insert rows or columns where you don’t want them to.

zeddy

Reply | Quote

Mark,

I don’t know of any VBA method to detect what you want, but you can use a ‘manual’ method:

Add some text to cell A65536. A warning message from Excel is then generated when any attempt to add rows is made.
The user can clear the cell if they absolutely have to insert a row.

You could probably monitor the status of the cell and re-enter data if it is cleared.

Hope this helps.

Jim Cone
San Francisco, CA
jim.coneZZZ@rcn.comZZZ

Reply | Quote

This won’t do anything except alert, but is simple.
If the range you don’t want disturbed is, say, a1:j25, Name it something imaginative, like “Range”.
In K1, put a formula like
=IF(ROWS(range)=25,””,”Are you Sure”)
Copy this to K25 (for a more decorative effect.)
Apply a conditional format to k1: whatever hideous combination of bright red background, bright yellow text and thick blue border is most likely to wake your user up.
The conditional format to be triggered when
cell value is equal to =”Are you Sure”
A similar approach to alerting when columns were added could be used.

Reply | Quote