Protecting cells with VBA (Excel 2003)

[nb0512]

In my application a user makes a choice using a dropdown box in cell k44.
Depending on the choice, I then run some code to put some data in some other
cells, k59 and k61-64, which I subsequently want to protect programmatically
from being changed manually.
I get an error saying the program cannot set the .Locked property of the
cells.

I have these cells unlocked in the sheet. In the code, I unprotect the sheet
before attempting to change the cells' properties. Below is the code I use.
Any ideas ?

Private Sub Worksheet_change(ByVal Target As Range)
....
....some Dim statements
....
Application.ScreenUpdating = False
Application.EnableEvents = False
Sheet10.Unprotect Password:="somepwd"

On Error GoTo ErrHand

....
....some other stuff
....

Err = "k44"

If Not Intersect(Range("k44"), Target) Is Nothing Then

Select Case Range("k44").Value

Case "...."

Case "...."


Case "Customer"
Range("L51").Value = "0"
Range("K51").Value = "0"

Range("k59").Value = "=VLOOKUP($K$35,'Pop
Addresses'!$A$2:$N$18,2)"
Range("k61").Value = "=VLOOKUP($K$35,'Pop
Addresses'!$A$2:$N$18,4)"
Range("k62").Value = "=VLOOKUP($K$35,'Pop
Addresses'!$A$2:$N$18,5)"
Range("k63").Value = "=VLOOKUP($K$35,'Pop
Addresses'!$A$2:$N$18,6)"
Range("k64").Value = "=VLOOKUP($K$35,'Pop
Addresses'!$A$2:$N$18,7)"
Range("k59").Locked = True---------jumps to ErrHand
here------- Range("k61:k64").Locked = True

....
....some other stuff
....

End If

ErrHand:
If Err <> "" Then
Result = MsgBox("Error occurred excuting " & Err & " change event",
vbOKOnly, "Error")
End If
ActiveSheet.Protect Password:="somepwd"
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

 

[Per Jessen]

Hi

If Sheet10 is the active sheet no error should appear, but as I read your
code, the macro will always execute the ErrHand code. If no errors occour
you need to skip the ErrHand and goto ActiveSheet.protect etc...

Snip...

End If
Goto ProtectSheet

ErrHand:
If Err <> "" Then
Result = MsgBox("Error occurred excuting " & Err & " change event",
vbOKOnly, "Error")
End If

ProtectSheet:
ActiveSheet.Protect Password:="somepwd"
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Best regards,
Per

 

[nb0512]

Thanks Per, my idea (see P.S.). However the error message does appear (Error
1004 if I leave out my ahem.. 'stuctured' error handling stuff). I built a
small test sheet to check this protection behavior with locking and unlocking
cells and it works perfectly. I suppose in the real program there is some
unwanted interaction between the Excel and VBA protection mechanisms. I'll go
over the Workbook code and modules again just in case...

P.S. In the full code there is Err = "<cellname>" above every section and
Err = "" just above the ErrHand label, so if the code executes normally, the
ErrHand will be skipped. If an exception occurs, the MsgBox shows the cell
name where it jumped out.

 

[Per Jessen]

Thanks for your reply.

I wouldn't use Err as my own variable, as Err is an object in VBA. It may
disturbe your error handler.

Isn't it possible to correct the code to avoid theese errors?

BTW: I would use Range("A1").formula="=vlookup(.... , inserting formulas
into cells.

If you don't find a solution, I can take a look at it if you mail the
workbook to me.

Best regards,
Per

 

[nb0512]

Thanks Per, but EUREKA !!!! Tremendous cheers and boost of morale.
I knew it had to be something silly. The cells I am trying to lock are
merged cells and Excel is not quite consistent in how it deals with
properties of merged cells. You can change the value, interior color and a
host of other properties by referring to the right most cell only without any
problem, but the 'locked' property requires reference to the range occupied
by the merged cells. In my case, repacing Range("k59").Locked with
Range("k59:l59").Locked and so forth did the trick.
Thanks for your help !

 

[Per Jessen]

Hi

Thanks for your reply.

I'm glad that you solved the problem.

Regards,
Per