Lists on a protected worksheet

[thebaldsoprano]

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?

 

[ShaneDevenshire]

Hi,

When a spreadsheet is protected in 2003 you can not add rows to it by moving
to the bottom cell of the list. The best you can do is allow the user to
Insert Rows (in the Spreadsheet Protection area and unprotect the cells in
the List area (Format, Cells, Protection) and then tell them to Insert a row
within the list area.

 

[Gord Dibben]

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