Allow "List" to work in a locked sheet
 

Although I have set its properties (FormatCellsProtectionUnlock) to
"Unlocked", List drop downs (situated above likewise unlocked cells in a
colum) will not function in a sheet that is otherwise locked.
Ideas?
--
Ken Curtis

Allow "List" to work in a locked sheet
 

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
"Ken Curtis" wrote in message
...
Although I have set its properties (FormatCellsProtectionUnlock) to
"Unlocked", List drop downs (situated above likewise unlocked cells in a
colum) will not function in a sheet that is otherwise locked.
Ideas?
--
Ken Curtis

Allow "List" to work in a locked sheet
 

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.

"Otto Moehrbach" wrote:

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
"Ken Curtis" wrote in message
...
Although I have set its properties (FormatCellsProtectionUnlock) to
"Unlocked", List drop downs (situated above likewise unlocked cells in a
colum) will not function in a sheet that is otherwise locked.
Ideas?
--
Ken Curtis

Allow "List" to work in a locked sheet
 

Otto

I believe OP is referring to DataListCreate 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

On Sun, 7 Sep 2008 10:58:22 -0400, "Otto Moehrbach"
wrote:

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
"Ken Curtis" wrote in message
...
Although I have set its properties (FormatCellsProtectionUnlock) to
"Unlocked", List drop downs (situated above likewise unlocked cells in a
colum) will not function in a sheet that is otherwise locked.
Ideas?
--
Ken Curtis

Allow "List" to work in a locked sheet
 

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

On Sun, 07 Sep 2008 08:34:59 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Otto

I believe OP is referring to DataListCreate 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

On Sun, 7 Sep 2008 10:58:22 -0400, "Otto Moehrbach"
wrote:

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
"Ken Curtis" wrote in message
...
Although I have set its properties (FormatCellsProtectionUnlock) to
"Unlocked", List drop downs (situated above likewise unlocked cells in a
colum) will not function in a sheet that is otherwise locked.
Ideas?
--
Ken Curtis