+ Reply to Thread
Results 1 to 5 of 5

trying to insert new row in protected worksheet on a table and keep the formulas intact

  1. #1
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    166

    trying to insert new row in protected worksheet on a table and keep the formulas intact

    I dont think you can add a new row in a table on a protected worksheet and pass down the formula from the row above to that new row?

    attached sheet,
    protect password cell8

    here I unlock these cells

    Screenshot_20200419131013.png

    these columns are locked and hidden, they contain the formulas.

    Screenshot_20200419131057.png

    the protect sheet settings

    Screenshot_20200419131129.png


    when I insert a new row, the formulas dont copy down.

    Screenshot_20200419131228.png

  2. #2
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016
    Posts
    660

    Re: trying to insert new row in protected worksheet on a table and keep the formulas intac

    Hi, you must also amend the cell properties of the range:

    Cheers
    Erwin
    PS. Sorry, no English version
    Attached Images Attached Images
    I started learning VBA because I was lazy ...
    Still developing.... being more lazy...

  3. #3
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    166

    Re: trying to insert new row in protected worksheet on a table and keep the formulas intac

    Thanks Eastw00d, so after creating the new row, I must unlock the sheet and copy the formulas down into the new row?

    If I choose to just hide but not lock the cells, which I presume is what you are showing? I noticed that anyone on the protected sheet can then delete the formulas from that cell in that Table. Sorry i`m still a bit lost on it.

  4. #4
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016
    Posts
    660

    Re: trying to insert new row in protected worksheet on a table and keep the formulas intac

    No, as far as I can see, you only have to do this once.
    I unlock "Geblokkeerd" i.e. "Blocked" for the whole area in the table.
    But the flaw is, you cannot see the formulas, but alas it is possible to delete them.
    Sorry, I do not know how to fix that.
    Cheers
    Erwin

  5. #5
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    166

    Re: trying to insert new row in protected worksheet on a table and keep the formulas intac

    its cool thanks Erwin for taking a look and explaining that, at least I know the difficulties at the moment.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] macro to insert row on last cell in a table of a protected worksheet
    By wwestlin in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 11-23-2017, 04:53 PM
  2. Insert Row and Copy Formulas in Protected Worksheet
    By anandsp87 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 11-04-2014, 01:04 AM
  3. [SOLVED] Insert row for table on protected worksheet
    By Justair07 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-23-2013, 07:37 AM
  4. Copy and paste insert rows with protected cells in protected worksheet
    By excel_gecko in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-24-2012, 05:50 PM
  5. Macro to insert new row in Excel 2010 table (ListObject) in protected worksheet
    By kinsley in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-10-2012, 02:57 PM
  6. Replies: 8
    Last Post: 12-11-2009, 04:36 PM
  7. How do I clear a worksheet of values, but leave formulas intact?
    By powers_marilyn@hotmail.com in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-18-2005, 01:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1