Protect Cells
 

All of the Cells in the Range("C40:K40,M40:U40") are unlocked, however as a
result of the following code only those cells which have the validation
added need to be unlocked. The rest can be locked.
My question - Is it possible to only unlock the cells with validation added
at runtime.

For Each mycell In Range("")
'***The following can be locked
If mycell.Value = "Hit" Then
With mycell.Offset(1)
.Validation.Delete
.Value = ""
End With
'***The following Must be unlocked
ElseIf mycell.Value = "Miss" Then
With mycell.Offset(1).Validation
.Delete
.Add Type:=xlValidateList, Formula1:="Right,Wrong"
.IgnoreBlank = True
.InCellDropdown = True
End With
End If
Next

Sandy

Protect Cells
 

Untested but I think this is what you are looking for...

Sub LockCells()
Dim rngValidation As Range

Cells.Locked = False
On Error Resume Next
Set rngValidation = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0

If Not rngValidation Is Nothing Then rngValidation.Locked = False
End Sub
--
HTH...

Jim Thomlinson


"Sandy" wrote:

All of the Cells in the Range("C40:K40,M40:U40") are unlocked, however as a
result of the following code only those cells which have the validation
added need to be unlocked. The rest can be locked.
My question - Is it possible to only unlock the cells with validation added
at runtime.

For Each mycell In Range("")
'***The following can be locked
If mycell.Value = "Hit" Then
With mycell.Offset(1)
.Validation.Delete
.Value = ""
End With
'***The following Must be unlocked
ElseIf mycell.Value = "Miss" Then
With mycell.Offset(1).Validation
.Delete
.Add Type:=xlValidateList, Formula1:="Right,Wrong"
.IgnoreBlank = True
.InCellDropdown = True
End With
End If
Next

Sandy

Protect Cells
 

Didn't get the chance to try it either - but I will report back.
Sandy

"Jim Thomlinson" wrote in message
...
Untested but I think this is what you are looking for...

Sub LockCells()
Dim rngValidation As Range

Cells.Locked = False
On Error Resume Next
Set rngValidation = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0

If Not rngValidation Is Nothing Then rngValidation.Locked = False
End Sub
--
HTH...

Jim Thomlinson


"Sandy" wrote:

All of the Cells in the Range("C40:K40,M40:U40") are unlocked, however as
a
result of the following code only those cells which have the validation
added need to be unlocked. The rest can be locked.
My question - Is it possible to only unlock the cells with validation
added
at runtime.

For Each mycell In Range("")
'***The following can be locked
If mycell.Value = "Hit" Then
With mycell.Offset(1)
.Validation.Delete
.Value = ""
End With
'***The following Must be unlocked
ElseIf mycell.Value = "Miss" Then
With mycell.Offset(1).Validation
.Delete
.Add Type:=xlValidateList, Formula1:="Right,Wrong"
.IgnoreBlank = True
.InCellDropdown = True
End With
End If
Next

Sandy

Protect Cells
 

Hi Jim
With the following adjustments it works beatifully:-

Dim rngValidation As Range
Dim rngPossibleValidation As Range

Set rngPossibleValidation =
Range("C14:K14,M14:U14,C15:K15,M15:U15,--Etc--,M23:U23")
rngPossibleValidation.Cells.Locked = True

'On Error Resume Next

Set rngValidation = Cells.SpecialCells(xlCellTypeAllValidation)
rngValidation.Cells.Locked = False

'On Error GoTo 0

'If Not rngValidation Is Nothing Then rngValidation.Locked = False

I have commented out (as you can see) the 'On Error' lines and the 'If Not
rngValidation Etc' - are they really needed?
Sandy

"Jim Thomlinson" wrote in message
...
Untested but I think this is what you are looking for...

Sub LockCells()
Dim rngValidation As Range

Cells.Locked = False
On Error Resume Next
Set rngValidation = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0

If Not rngValidation Is Nothing Then rngValidation.Locked = False
End Sub
--
HTH...

Jim Thomlinson


"Sandy" wrote:

All of the Cells in the Range("C40:K40,M40:U40") are unlocked, however as
a
result of the following code only those cells which have the validation
added need to be unlocked. The rest can be locked.
My question - Is it possible to only unlock the cells with validation
added
at runtime.

For Each mycell In Range("")
'***The following can be locked
If mycell.Value = "Hit" Then
With mycell.Offset(1)
.Validation.Delete
.Value = ""
End With
'***The following Must be unlocked
ElseIf mycell.Value = "Miss" Then
With mycell.Offset(1).Validation
.Delete
.Add Type:=xlValidateList, Formula1:="Right,Wrong"
.IgnoreBlank = True
.InCellDropdown = True
End With
End If
Next

Sandy