insert row in protected sheet by keeping formula

Anonymous
2011-08-13T08:15:07+00:00

Hi

I have a protected sheet contain formula in each row depends to above row( for example C2=C1+1; C3=C2+1;...).and cells contained these formula are locked.

I like to allow users to insert row everywhere by keeping formula without unprotecting the sheet. When I am clicking "insert row" while protecting the sheet, users could insert a row but the locked inserted cells are not including any formula and excel doesn't permit to copy formula of above cells. is there any solution?

Thanks

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
HansV 460.5K Reputation points MVP Volunteer Moderator
2011-08-14T10:16:32+00:00

If you just want the result of the formula, try

ActiveSheet.Range("E" & r).Value = ActiveSheet.Range("E1").Value

Was this answer helpful?

1 person found this answer helpful.
0 comments

42 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-08-02T23:45:42+00:00

    Actually I came back at this fresh and started messing with it again and it worked. Whoo Hoo! Thanks a lot. I had been banging my head for a while on this one.

    Was this answer helpful?

    0 comments
  2. HansV 460.5K Reputation points MVP Volunteer Moderator
    2014-08-02T19:05:42+00:00

    Could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as Microsoft OneDrive (https://onedrive.live.com), FileDropper (http://filedropper.com) or DropBox (http://www.dropbox.com). Then post a link to the uploaded and shared file here.

    Or register at www.eileenslounge.com (it's free) and post a message in the Excel forum. You can attach files up to 250 KB to a post there (zipped if necessary).

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-08-02T19:00:42+00:00

    I can see how you did everything in the demo but when I open a spreadsheet to duplicate the VBA code it does not work. Is there a setting or step I am missing or something? Also I tried the tables earlier as a suggestion from a friend it works only when unprotected. So this VBA looks like the viable option I just can't seem to get it to work for some reason.

    I create my table, then create the VBA code, I enabled all macros, and saved my file as macros enabled, the little insert row button never comes up.

    I really appreaciate your help and responsiveness, however, I will be away from the computer for a few hours so you may not hear from me in a little bit if you reply again.

    Thanks

    Was this answer helpful?

    0 comments
  4. HansV 460.5K Reputation points MVP Volunteer Moderator
    2014-08-02T18:03:18+00:00

    I have uploaded a sample workbook to DropBox:

    https://www.dropbox.com/s/wco8l3hg02exii6/InsertDemo.xlsm

    The worksheet has been protected with password secret. The VBA code is not protected.

    The command button lets you insert a row if you select a cell in the data (below the first row with data). Formulas (in this example in column D only, but you could add more) will be copied automatically.

    Another option would be to define a table. Excel will automatically propagate formulas in a table row when you insert a new row.

    Was this answer helpful?

    0 comments