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
280 views

1 answer to this question.

0 votes

The only thing I can offer is a workaround based on https://stackoverflow.com/a/7479837/17017616

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
    Application.Undo
    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
883 views
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
452 views
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
329 views
0 votes
1 answer

Retrieve epay.info 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
980 views
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
3,331 views
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
637 views
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
854 views
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
1,291 views
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
2,014 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP