make few cells editable and few cells to be protected in excel

Question

I have an excel sheet with data from row 8 till end and from column A to AZ I've written a macro where when a user selects a cell it takes the row address and will prompt for the start column name and end column name , so the sheet gets bloked at those cells. the problem is if I want to insert row at the end it is not allowing me to add. how to do insert a row in this type of situation.

this excel is distributed to the users and the users should be able to add rows but shouldn't able to edit the columns which I locked. how to do this? this is the macro I've written:

Sub Row_Locker()
Dim locat As String
Dim colstart As String
Dim colend As String
Dim topath As String
ActiveSheet.Protect Password:="mbt"
ActiveSheet.Unprotect
rlocat = ActiveCell.Row
clocat = ActiveCell.Column
colstart = InputBox("enter the start column name")
colend = InputBox("enter the end column name")
topath = colstart & "8" & ":" & colend & rlocat
Cells.Select
' unlock all the cells
Selection.Locked = False
' next, select the cells (or range) that you want to make read only,
' here I used simply A1
Range(topath).Select
' lock those cells
Selection.Locked = True
' now we need to protect the sheet to restrict access to the cells.
' I protected only the contents you can add whatever you want
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False
End Sub

the excel sheet looks like this, so if I select the yellow box the column contents above it and the column contents beside it will be blocked if run the macro. and type "N" for the first input box and "O" for the second input box

Answer 1

Try this:

ActiveSheet.Protect Password:="your pw", AllowInsertingRows:=True ' , other if needed,

Ref: WorkSheet.Protect

expression.Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)

expression: A variable that represents a Worksheet object.