Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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,

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  3. #3
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

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

    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

  4. #4
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •