How to lock a row with VBA

[Mootastic]

I am having difficulty figuring out how to lock a row using VBA code. I know I can unlock the entire document, lock a single row and then protect sheet, but doing it that way does not allow me to open and collapse groups without first unlocking the document. So I am looking for help on a VBA code that would let me lock a single row. I have looked around for information on this for awhile, and I came across this...

Private Sub Worksheet_change(ByVal Target As Excel.Range)
If Target.Address <> "$A$1" Then If Target.Address <> "$B$1" Then Exit Sub
Application.EnableEvents = False
MsgBox "Error, incorrect operation.", 48, "Sorry, I'm protected."
Application.Undo
Application.EnableEvents = True
End Sub

That lets me block single cells, and I can technically go on with that to block a row, but I will need to be able to add a new row to be locked occasionally and I would rather not have to go through a long process of editing the 1's to 2's and so on. Any help would be appreciated.

 

[doofusboy]

Locking cells [or rows] is only operational if a worksheet is protected. That is just the way Excel works.

If you want different behavior, you'll need to write code for it [similar to what you posted]. If you need to 'lock' other rows in the future, you could adapt the code you showed to accept a range input parameter [the row to be 'locked'].

 

[Mootastic]

I don't know how to add in a range input parameter.

 

[tybalt]

Did this ever get solved? I'd like to do essentially the same thing. I just want to protect the first row (header of a table) of ever worksheet.