Further to Shane's reply................

This event code will allow you to select anywhere within the List and the
sheet will become unprotected for inserting/deleting rows and columns within
the List only.

The ListObject Range will expand with inserted rows and columns

Select anywhere outside the List and sheet will re-protect.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim objlist As ListObject
Set objlist = Me.ListObjects(1)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, objlist.Range) Is Nothing Then
Me.Unprotect Password:="justme"
Else
With Me
.Protect Password:="justme"
.EnableSelection = xlUnlockedCells
.EnableSelection = xllockedCells
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste into that module.

Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP


On Thu, 11 Sep 2008 12:16:15 -0700, thebaldsoprano
wrote:

We use Excel 03. When I protect my worksheet my list stops automatically
generating new rows. I've given permissions for people to insert & format
rows. What do I need to do?