Prevent a user from adding or removing row s of a Range but allow him to do so on another Range on the same sheet

0 votes
Two specified ranges on my sheet are Titles (A11:O15) and Contents (A18:O30).

I'm trying to find a solution to stop a user from adding or removing any rows of titles, but he must still be able to change the content of those titles.

Contrarily, the user has complete freedom to alter the contents.

How can I make my sheet's first 17 lines editable while preventing the insertion or deletion of rows before row 17?
Feb 21, 2023 in Others by Kithuzzz
• 38,010 points

1 answer to this question.

0 votes

The only thing I can offer is a workaround based on

To prevent users from adding/removing rows and/or columns to certain cells, you must first create a named range for those cells. The range in my example has the name protected Area and spans 32 total cells.

You then insert this code into the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    Const referenceCellCount = 32
    Static recursionGuard As Boolean
    Dim rngProt As Range
    If recursionGuard = True Then
        Exit Sub
    End If
    Set rngProt = ThisWorkbook.Names("protected_Area").RefersToRange
   ' Adding or removing Rows in the protected area will
   ' change the size of the range and thus the total count of cells
    If referenceCellCount = rngProt.Cells.Count Then
        Exit Sub
    End If
    recursionGuard = True
    recursionGuard = False
    MsgBox "Foo must not..."

End Sub

Make sure that referenceCellCount matches your case.

answered Feb 21, 2023 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

In a excel formula I need to create a list of names on one sheet based upon criteria/data of another sheet

The final formula is: =IF(ROWS($H$3:H3)<=$I$1,INDEX(Personnel! ...READ MORE

answered Nov 25, 2022 in Others by narikkadan
• 63,700 points
0 votes
1 answer

How do I get it to select a single row based on the value?

Try this: Sub MoveBasedOnValue2() Dim ...READ MORE

answered Jan 20, 2023 in Others by narikkadan
• 63,700 points
0 votes
1 answer

VBA - user input of row value to use in a cell range

Use this: Range("C" & c & ":I" & ...READ MORE

answered Feb 7, 2023 in Others by narikkadan
• 63,700 points
0 votes
1 answer

Retrieve Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer

How do you populate a google sheets/excel column with cells from a column in another sheet in the same document?

You have two options on chronology: sheet-by-sheet =QUERY({Sheet1!X:Z; Sheet2!X:Z; ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 63,700 points
0 votes
1 answer

How to insert a new row in the specified cell only, of Excel sheet using c#?

I have this worksheet with a matrix ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 63,700 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP