need code for disable selection of locked cells

Got code
Sub protect()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.EnableSelection = xlUnlockedCells
ws.protect Password:=""
Next ws

End Sub

I need to disable selection of locked cells
Is there a function like ws.DisableSelection = xlLockedCells?

Hi David,

> I need to disable selection of locked cells
> Is there a function like ws.DisableSelection = xlLockedCells?

That is what your code does.

If, as in your code, you set the EnableSelection to unlocked cells, only
unlocked cells can be selected - locked cells become unselectable.

---
Regards,
Norman



"David Lewis" <[email protected]> wrote in message
news:[email protected]...
> Got code
> Sub protect()
>
> Dim ws As Worksheet
>
> For Each ws In ActiveWorkbook.Worksheets
> ws.EnableSelection = xlUnlockedCells
> ws.protect Password:=""
> Next ws
>
> End Sub
>
> I need to disable selection of locked cells
> Is there a function like ws.DisableSelection = xlLockedCells?
>

hi David

This will only allow unlocked cells to be selected as long as the sheets are
protected. You code does not unprotect the sheets first (although you could
get around this by setting the userinterface to true).

Sub test()
Dim ws As Worksheet
For Each ws In Worksheets
With ws
..Unprotect
..EnableSelection = xlUnlockedCells
..Protect
End With
Next ws
End Sub

--
XL2002
Regards

William

[email protected]

"David Lewis" <[email protected]> wrote in message
news:[email protected]...
| Got code
| Sub protect()
|
| Dim ws As Worksheet
|
| For Each ws In ActiveWorkbook.Worksheets
| ws.EnableSelection = xlUnlockedCells
| ws.protect Password:=""
| Next ws
|
| End Sub
|
| I need to disable selection of locked cells
| Is there a function like ws.DisableSelection = xlLockedCells?
|

You can modify the Scroll Area to restrict which cells can be selected... It
requires a single continuious range of cells though. The user can only move
within that range of cells. Otherwise there is nothing that I know of to keep
the user from selecting a cell. Locking it only protects it from being
updated. You can hide the formula of a locked cell if that helps...

HTH

"David Lewis" wrote:

> Got code
> Sub protect()
>
> Dim ws As Worksheet
>
> For Each ws In ActiveWorkbook.Worksheets
> ws.EnableSelection = xlUnlockedCells
> ws.protect Password:=""
> Next ws
>
> End Sub
>
> I need to disable selection of locked cells
> Is there a function like ws.DisableSelection = xlLockedCells?
>
>

All I know is my code locks the page. When I save and close and go back I can select the cells again.
If I manually lock the cells and uncheck the locked cells option then it works properly.

"Norman Jones" <[email protected]>
|>Hi David,
|>
|>> I need to disable selection of locked cells
|>> Is there a function like ws.DisableSelection = xlLockedCells?
|>
|>That is what your code does.
|>
|>If, as in your code, you set the EnableSelection to unlocked cells, only
|>unlocked cells can be selected - locked cells become unselectable.
|>
|>---
|>Regards,
|>Norman
|>
|>
|>
|>"David Lewis" <[email protected]> wrote in message
|>news:[email protected]...
|>> Got code
|>> Sub protect()
|>>
|>> Dim ws As Worksheet
|>>
|>> For Each ws In ActiveWorkbook.Worksheets
|>> ws.EnableSelection = xlUnlockedCells
|>> ws.protect Password:=""
|>> Next ws
|>>
|>> End Sub
|>>
|>> I need to disable selection of locked cells
|>> Is there a function like ws.DisableSelection = xlLockedCells?
|>>
|>

David,

You probably have your answer years ago but, I want to post something relevant to your question. This is a strange error I had and and found nothing on the web regarding a fix or work-around and thought it will be helpful for something to be out there in the ether.

First, for your question, use "xlNoSelection" to uncheck the ability to select locked and unlocked cells for sheet protection. Use "xlUnlockedCells" to check the box to allow the use of unlocked cells with sheet protection. Now, here is the kicker... For selecting 'locked' cells I could find nothing so I experimented with the obvious "xlLockedCells" but, kepted receiving a "Variable not defined" error. This seemed an obvious error if Excel did not have it as a defined function, so, long story short, I commented out "Option Explicit" and it worked! Weird but, not unheard of with Microsoft. ;-)

Use "xllockedcells" to check the boxes to allow the use of locked and unlocked cells with sheet protection. I do not follow the typical capitalization format being this is not a recognized function within Excel.

By-the-way, I'm using Excel 2010 and 2007 - worked out this in 2010.

**********

'Option Explicit
________________________________

Sub AllowAllOnProtectedSheet()

'Allow EVERYTHING on protect

ActiveSheet.Unprotect Password:=""

'ActiveSheet.EnableSelection = xlNoSelection
ActiveSheet.EnableSelection = xlUnlockedCells
ActiveSheet.EnableSelection = xllockedcells

ActiveSheet.Protect Password:="", DrawingObjects:=False, Contents:=True, Scenarios:= False, _
AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows:=True, _
AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True

End Sub
________________________________


Sub AllowNothingOnProtectedSheet()

'Allow NOTHING on protect

ActiveSheet.Unprotect Password:=""

ActiveSheet.EnableSelection = xlNoSelection
'ActiveSheet.EnableSelection = xlUnlockedCells
'ActiveSheet.EnableSelection = xlLockedCells
ActiveSheet.Protect Password:="", DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

**********

Self taught in Excel and VBA so forgive me if my terminology may be off. Hope this helps!!

Dale