Excel Tables

[tiredofit]

My worksheet contains a headings row (cells A1 to C1) and 1 row of data (cells A2 to C2).

I want to prevent users from altering the headings, so I add this code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Row = 1 Then
   
        Application.EnableEvents = False
       
        Application.Undo
       
        Application.EnableEvents = True
   
    End If
   
End Sub

If the user tries to alter any value in cells A1 to C1, the code reverses it.

If the user tries to type in any cell in row 1, eg D1 or E1, the code also reverse it, which is what I want.

However, if the range (A1 to C2) were converted into a table (by pressing Ctrl+T), things don't quite go according to plan.

If the user tries to alter any value in cells A1 to C1, the code reverses it, (as expected).

However, if the user tries to type in any cell in row 1, eg D1 or E1, Excel extends the table range to include this new cell address, then the code reverses this last action (the extending of the table).

Whatever value was entered still remains.

How can I change it so the value typed is reversed as well?

Thanks

 

[KPKP]

Could you instead:
Select row A > Format Cells > Protection > Locked
Protect Sheet (apply a password)
Protect Workbook (apply a password)

 

[KPKP]

Sorry, just realized my previous post was a bit misleading...
Excel defaults all cells to Protected status, therefore would have to:

Select the entire tab (click the triangle in top left corner of tab)
Set all cells to Unlocked
Then select row 1 > Format Cells > Protection > Locked
Protect Sheet (apply a password)
Protect Workbook (apply a password)

 

[tiredofit]

Sorry, just realized my previous post was a bit misleading...
Excel defaults all cells to Protected status, therefore would have to:

Select the entire tab (click the triangle in top left corner of tab)
Set all cells to Unlocked
Then select row 1 > Format Cells > Protection > Locked
Protect Sheet (apply a password)
Protect Workbook (apply a password)

Thanks for your suggestions but I'd rather not resort to passwords and protecting the sheet.

Instead, I have decided to delete the columns to solve this particular problem.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column > 3 Then
    
        With Application
        
            .ScreenUpdating = False
            .EnableEvents = False
        
        End With
        
        Me.Columns((Target.Column)).Delete

        With Application
        
            .ScreenUpdating = True
            .EnableEvents = True
    
        End With
    
    End If

End Sub