Cursor and Sheet Protection may09

[Neal Zimm]

Hi All,
I use the procs below to protect and unprotect sheets.

At times, (and I can't find the 'pattern'), I lose the
cursor after protecting the sheet.

After 'manual' protection the cursor moves to a unlocked cell.

If I've changed a locked cell in a macro, do I have to select a
Not .Locked cell to make sure the cursor is there
after protection ?

Thanks.



Sub UNprotectStdId(Ws As Worksheet, Optional Id As String = "")
If Not Ws Is Nothing Then Ws.UNprotect Id
End Sub

' Code to change a range

Sub ProtectStdId(Ws As Worksheet, Optional Id As String = "", _
Optional SelectionType As Long = xlUnlockedCells)

If Not Ws Is Nothing Then
Ws.Protect Id, AllowFormattingCells:=True
Ws.EnableSelection = SelectionType
End If
End Sub

 

[Barb Reinhardt]

You can find unlocked cells like this

Dim r as Excel.Range

for each r in WS.UsedRange
if not r.locked then
'Unlocked
else
'Locked
end if
next r

 

[Neal Zimm]

Wow Barb, that was fast ....

I guess I was not clear in my question, I know about the .locked property
and how to find cells that are/not locked.

To restate, when I manually protect a sheet, the cursor always moves to a
not locked cell if there is one.

In my VBA code however, there are times when I unprotect a sheet to make
changes to locked cells, then, after protecting the sheet, when I go back to
look at it, there is NO cursor to be found. When I unprotect the sheet
and then re-protect it, the cursor magically comes back to life.

How do I stop this from happening ? I am hoping that your answer meant
that I do indeed have to find a unlocked cell before protecting the sheet.

But if that's true, that why does that never happen manually ?

Thanks,
Neal