Allow "List" to work in a locked sheet

[Ken Curtis]

Although I have set its properties (Format>Cells>Protection>Unlock) to
"Unlocked", List drop downs (situated above likewise unlocked cells in a
colum) will not function in a sheet that is otherwise locked.
Ideas?

 

[Otto Moehrbach]

Ken
I don't follow all of that. Do this. Unprotect the sheet. Select the
Data Validation cell and set it to unlocked. Protect the sheet. The DV
cell should function normally. HTH Otto

 

[FC]

Hi Kent, the dropdown you are referring to may be the filtered columns.
If that is, then you should check "use filter" when protecting the worksheet.
If is regular dropdown (Data Validation) then it should work fine as Otto
explained.

 

[Gord Dibben]

Otto

I believe OP is referring to Data>List>Create List in 2003

And yes, the arrows in top row of a List disappear when the sheet is
protected even when the cells within the List are unlocked.

I don't know of a workaround.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

To OP

You could use sheet event code to unprotect the sheet when you select any
cell within the List and re-protect when you select outside the List.

This quickly-cobbled together event code will work on a set list range.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "A1:F28"
Dim cell As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Unprotect Password:="justme"
Else
Me.Protect Password:="justme"
End If

ws_exit:
Application.EnableEvents = True
End Sub

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

Copy/paste into that sheet module. Adjust the F1:F28 range to suit.

I will look at code which will adjust to an expanding List


Gord