N
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
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