+ Reply to Thread
Results 1 to 4 of 4

Insert New Row in Protected Sheet and Copy Some Formatting and Formula From Row Above

  1. #1
    Registered User
    Join Date
    09-05-2016
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Ms Office Professional Plus 2013
    Posts
    2

    Insert New Row in Protected Sheet and Copy Some Formatting and Formula From Row Above

    Hi,
    Im new in using macro.
    So I got a sheet that contain information about master budget in finance. In example I got the A column for account number, B column for account name, C for Department name, D, E, F, G is user input manually, H is inflation value (that is constant), and I is total (let say it if the cell is I3 so the formula would be = D3+E3+F3+G3).
    The data is start from the 7th row tho the 52th row.
    What can I do if I want to protect thee sheet (to protect the formula in column I), but when I insert a row (bc sometimes user need to insert new row when the data is too many), it will be :
    1. Copy value inflation in column H above the row
    2. Copy formula from column I above the row

    I would be really thankfull if somebody help me on this matter

  2. #2
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Insert New Row in Protected Sheet and Copy Some Formatting and Formula From Row Above

    ssariyunita,
    Can you please reexplain? This section is very confusing, not sure what you're asking for:
    PHP Code: 
    What can I do if I want to protect thee sheet (to protect the formula in column I), but when I insert a row (bc sometimes user need to insert new row when the data is too many), it will be :
    1. Copy value inflation in column H above the row
    2. Copy formula from column I above the row 
    Are you wanting to protect the sheet, but also allow your user to insert rows?
    Or are you wanting to protect Column I, or just the formulas in Column I?
    And what do your 1. and 2. mean in the context of protecting whatever you want to protect?

  3. #3
    Registered User
    Join Date
    09-05-2016
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Ms Office Professional Plus 2013
    Posts
    2

    Re: Insert New Row in Protected Sheet and Copy Some Formatting and Formula From Row Above

    Jomili,
    Thanks for your respond
    The problem is I want to make make a protected sheet that when I insert a new row, the new inserted row will contain some value from above (in specific column), and also copy the formula from row above (also in specific column).

  4. #4
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Insert New Row in Protected Sheet and Copy Some Formatting and Formula From Row Above

    Okay, I think we've got you solved. I borrowed from some cool old code from Colo. I've provided an example workbook. Let me walk through it:

    First, we assume the worksheet is protected, with the password "12345", and in the protection we've allowed our user to insert or delete rows. Note that deleting a row from a protected sheet requires that the cells in that row are not locked.

    In the Worksheet module (in the example it's "Sheet15(Sample") we put the code that starts and ends it all based on activating and deactivating the sheet:
    Please Login or Register  to view this content.
    In a standard module paste the code below. When Event Hack runs, it assigns the "JudgRng" macro, which is where I did all of the changes. I've tried to comment it all, but the process it follows is:
    1) IF a single row is selected
    a) Unprotect the sheet
    b) Insert the new row
    c) Unlock the new row
    d) Copy Value or Formulas (I've given you lots of options) to the new row. I put a STOP in the code so you can figure out exactly which option(s) to go with.
    e) Reprotect the sheet


    The only thing that might still need to be done is, I assume at some point you want that new line locked so it won't be deleted later. We could do that on a Save event, or a workbook.close event, or a workbook.open event, or you can do it manually at your convenience.
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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] Insert row and copy formula in protected sheet
    By impresxy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-11-2017, 02:57 AM
  2. Insert Row and copy formula in protected sheet
    By cangel2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-26-2016, 09:20 AM
  3. Macro to copy and insert down several rows in protected sheet
    By Sardokar21 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-28-2015, 03:15 AM
  4. Replies: 3
    Last Post: 07-26-2014, 06:12 AM
  5. Insert Row on Protected Sheet and Auto Copy Formulas
    By ltradey in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-17-2014, 05:46 AM
  6. Replies: 1
    Last Post: 03-29-2014, 09:56 AM
  7. Insert row and then copy above formula on protected sheet
    By Rinto in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-13-2013, 02:45 AM

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