Lose Cursor After Protecting Sheet

[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

 

[Héctor Miguel]

hi, Neal !

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 ?

- does the "pattern" could it be when "the worksheet" has no more unlocked cells ?

- how (exactly) do you call each procedure, given they use "optional" arguments ?

hth,
hector.

__ exposed procedures __

 

[Neal Zimm]

Hi Hec,

does the "pattern" could it be when "the worksheet" has no more unlocked
cells ?,
No, in my testing, after protecting the sheet, there are always
unlocked cells to the right or down from the protected cell. I always
leave an "extra" cell unlocked for this purpose in a protected sheet.

how (exactly) do you call each procedure, given they use "optional"
arguments ?
Same as calling any other Sub or Function with Args(arguments)
You should become familiar with optional args, they can be a great help.

In summary, you can assign a default value to the optional variable IN
the Sub. If you want the default value, you do not assign a value in the
call statement.

If you want a DIFFERENT value than the default, then the variable
appears in the Call statement with the value you assign.

Sub UNprotectStdId(Ws As Worksheet, Optional Id As String = "")

For the unprotect sub, if the worksheet I want to unprotect is protected,
but NOT password protected then I use

Call UNprotectStdId(activesheet) 'or some other Ws object.
'note in the Sub's code: Ws.Unprotect Id 'Id is the password and
takes on the optional value of null, i.e. NO password.

If the sheet to be unprotected is password protected, then the call
looks like:
Call UNprotectStdId(activesheet, PW) 'where PW is a variable that
'contains the password. It overrides the optional value of null.

'Note, in my case, what makes the PW 'work' is that the code is in
' an AddIn which is protected from user's view, so the PW value
'cannot "easily" be viewed.

Neal