Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cursor and Sheet Protection may09
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 -- Neal Z |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cursor and Sheet Protection may09
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" wrote: 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 -- Neal Z |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cursor and Sheet Protection may09
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 -- Neal Z "Barb Reinhardt" wrote: 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" wrote: 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 -- Neal Z |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Protection Best Practice: AKA: Real Sheet Protection | Excel Discussion (Misc queries) | |||
Excel Data Protection- AKA: Sheet/Macro Password Protection | Setting up and Configuration of Excel | |||
move cursor on one sheet moves cursor on all sheets | Excel Discussion (Misc queries) | |||
Putting Cursor in Cell A1 of each Worksheet, then ending cursor on smalest sheet name according to VBA Editor | Excel Programming | |||
Sheet protection error msg - Unrequested sheet activation | Excel Programming |