+ Reply to Thread
Results 1 to 4 of 4

Protected sheet how do I allow row insertion on specific rows

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    12-07-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2021
    Posts
    126

    Protected sheet how do I allow row insertion on specific rows

    Hi,
    I have a protected sheet:

    Public Sub SheetProtectionOn()
    'ShortKey Ctrl-Shft-'P'
        Dim bUpdate As Boolean
        Dim oSh As Object
        bUpdate = Application.ScreenUpdating
        Application.ScreenUpdating = False
        For Each oSh In Worksheets
            oSh.Protect _
            UserInterfaceOnly:=True, _
            AllowSorting:=True, _
            AllowFiltering:=True, _
            AllowFormattingCells:=True, _
            AllowFormattingRows:=True, _
            AllowInsertingRows:=True, _
            AllowUsingPivotTables:=True, _
            Password:="Interxion"
        Next
        Application.ScreenUpdating = bUpdate
    End Sub
    I only want to have the user insert a row in a row/range with unprotected cells.
    With above code it is AllowInsertingRows:=True or False (all or nothing)
    How would I do this ?

    Thanks for helping,
    Arnold

  2. #2
    Forum Contributor
    Join Date
    12-07-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2021
    Posts
    126

    Re: Protected sheet how do I allow row insertion on specific rows

    Please ignore this double post. I got a Server Error while posting but apparently it was posted afterall. (Can I delete this myself ?)

  3. #3
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Protected sheet how do I allow row insertion on specific rows

    I'm not sure there is an easy way to do this. The only thing I can think of would be to set allowinsertingrows to true but then create an event for "before insert row" and use code to only allow this to happen if the cells are unprotected.

    Unfortunately excel does not have a "beforeinsert" event so you need to use a set-up similar to the one in this thread:
    http://www.mrexcel.com/forum/excel-q...row-event.html

  4. #4
    Forum Contributor
    Join Date
    12-07-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2021
    Posts
    126

    Re: Protected sheet how do I allow row insertion on specific rows

    Hello yudlugar,
    Thanks for your help.
    I will be looking into your suggestions this evening...

+ 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] Allow insertion of row in protected sheet
    By Scofield24 in forum Excel General
    Replies: 5
    Last Post: 09-07-2013, 06:48 AM
  2. Replies: 0
    Last Post: 09-17-2012, 11:10 AM
  3. Delete rows in a protected sheet with protected cells
    By Maranwe in forum Excel General
    Replies: 7
    Last Post: 10-31-2011, 08:12 AM
  4. Cannot Outline Rows in protected sheet
    By helmekki in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-20-2005, 08:50 AM
  5. [SOLVED] how to hide rows in a protected sheet
    By Prakash in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-18-2005, 11:06 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