+ Reply to Thread
Results 1 to 3 of 3

How to prevent row insertion, column deletion, but allow certain values to be changed.

  1. #1
    Forum Contributor
    Join Date
    05-17-2017
    Location
    Tallahassee
    MS-Off Ver
    15.32
    Posts
    206

    How to prevent row insertion, column deletion, but allow certain values to be changed.

    Hi,

    I am trying to prevent rows to be added between rows 1-11, and prevent any columns added in the worksheet. I'd also like no values to be changed in rows 1-11. However, I'd like to be able to add rows anywhere else in the spreadsheet and edit values anywhere else as well. How would I do this. Maybe this doesn't need VBA code but this is seemingly difficult.

  2. #2
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: How to prevent row insertion, column deletion, but allow certain values to be changed.

    Maybe try this (without VBA) :

    Step-1
    1. Select all cells in the worksheet
    2. right click, choose "Format Cells..."
    3. Choose "Protection" tab
    4. Have the "Locked" box unticked
    5. Click OK

    Step-2
    6. Block row 1 to 11
    7. right click, choose "Format Cells..."
    8. Choose "Protection" tab
    9. Tick the "Locked" box
    10. Click OK

    Step-3
    11. Choose the "Format" menu tab
    12. Choose "Protect Sheet...."
    13. Tick the "Select Unlocked Cells"
    14. Tick the "Insert Rows"
    15. Tick the "Delete Rows" (optional, depends on you)
    16. Leave the rest unticked
    17. Click OK.

    prevent any columns added in the worksheet
    Because on step-3 you don't tick the "Insert Columns", it will prevent the user to add column in the sheet.

    I am trying to prevent rows to be added between rows 1-11
    I'd also like no values to be changed in rows 1-11
    Because step-2 and because on step-3 you don't tick the "Select Locked Cells", user won't be able to click any cell within row 1 to 11.
    Hence, there'll be no values can be changed in row 1 to 11 and there can't be rows to be added/inserted between row 1 to 11.

    I'd like to be able to add rows anywhere else
    Because step-14, then user can add rows anywhere besides row 1 to 11.
    To add (or delete if you do step 15) row, right click the row number outside row 1 to 11.
    There you have the option to insert/add (and delete if you do step 15) row.


    edit values anywhere else as well
    Because step-2 causing rows beside row 1 to 11 still Unlocked, then user can edit values on any cell outside row 1 to 11


    Hope this get what you want.
    Last edited by karmapala; 09-21-2019 at 03:33 PM.

  3. #3
    Forum Contributor
    Join Date
    05-17-2017
    Location
    Tallahassee
    MS-Off Ver
    15.32
    Posts
    206

    Re: How to prevent row insertion, column deletion, but allow certain values to be changed.

    Thank you. This worked :D

+ 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. Prevent certain values from being changed when using autofill
    By foymichaelc in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-22-2018, 12:36 PM
  2. insertion,deletion restriction in excel
    By aparnawangu in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-13-2017, 11:20 AM
  3. Replies: 10
    Last Post: 05-30-2016, 06:25 AM
  4. [SOLVED] Automatic Line Insertion - Deletion
    By katip16487 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-17-2014, 06:52 AM
  5. Prevent workbook deletion or create the copy upon the deletion?
    By ciapul12 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-29-2014, 04:35 PM
  6. Allow Deletion, Prevent Insertion of Row
    By yay_excel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-23-2011, 06:44 PM
  7. Insertion/deletion of columns, via VBA, depending on variables
    By wmfinance in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-01-2010, 10:31 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