Hi Ivan and Gord
Have checked Ivan's original code and it does work correctly when the sheet
is unprotected.

"johnsail" wrote:

Hi Gord

Changed routine as you suggested (I commented out the first bit of Ivan's
code - Yes???)

Routine still stops with same error when col D = "Mileage" but does not stop
when col D < "Mileage".

In both cases the cursor moves to next row Col B.

In both cases if you go back to the previous row the correct cells have been
unlocked and the cursor now moves to them. (It is just as if the unlock
didn't happen quickly enough to allow the cursor on to the newly unlocked
cell OR the next cell to get the focus is held somewhere and that value needs
changing in the code)

The resetting of the values when D changes from "Mileage" to non-"Mileage"
or vice versa does not now work nor does it work if D is deleted. However I
have played about with so many options now that I am beginning to wonder
whether my statement regarding Ivan's original code was in fact true. I am
going back to check on that.

Regards

"Gord Dibben" wrote:

What happens if you unprotect, do the work by code then re-protect.

ActiveSheet.Unprotect Password:="justme"

do your stuff here

ActiveSheet.Protect Password:="justme"


Gord Dibben MS Excel MVP

On Fri, 4 Apr 2008 15:20:00 -0700, johnsail
wrote:

Ivan
Yes that extra bit is in the code - I didn't alter that so I did not bother
to include it in my last reply.

Have done some investigating without the protection being on and it would
seem that the cells are getting protected / unprotected and the clearing of
cell D does wotk and does replace the formula.

However with the sheet protected the code stops with the error quoted.

Consequently the cursor is not allowed on to cell E for data entry (or cell
J if the entry in D is not Mileage)

regards

John

"Ivyleaf" wrote:

On Apr 5, 2:32 am, johnsail
wrote:
Ivan Hi

First thanks for the quick response.

Have put the formula in col K and the help cell in col L.

So have changed your routine to this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim OldVal, CaseE As Boolean

If Target.Column = 4 Then
OldVal = Target.Offset(0, 8).Value
CaseE = IsEmpty(Target)
If Target = "Mileage" Then
Target.Offset(0, 1).Locked = False
Else
Target.Offset(0, 6).Locked = False
End If
If (OldVal = "Mileage" And Target < "Mileage") Or CaseE Then
With Target.Offset(0, 1)
.ClearContents
.Locked = True
End With
End If
If Target < OldVal And Target = "Mileage" Or CaseE Then
With Target.Offset(0, 6)
.Formula = Target.Offset(0, 7).Formula
.Locked = True
End With
End If
Target.Offset(0, 8) = Target.Value
End If
End Sub

When running the code it gives the error:
Run Time error 1004
Unable to set Locked property of the Range Class

The debugger highlights the line:
Target.Offset(0, 6).Locked = False

Have I changed things incorrectly?

John



"Ivyleaf" wrote:
On Apr 5, 12:34 am, johnsail
wrote:
Hi
Have a worksheet of 40 rows and cols A to K.
Only cols B,C and D are unlocked.
Data is entered along each row.
Within each row
a) If the data entered in cell D = "999" then cell in col E should be
unlocked.
b) If data < "999" then cell in col J should be unlocked.
c) If D is changed from "999" to something else then contents of E need to
be deleted and the cell locked.
d) If D is changed to "999" from any other value the contents of cell in col
J need to be deleted and replaced with a formula and then locked.(The formula
is held in a hidden cell K on each row).
e) If the contents of cell D are deleted then both (c) and (d) above need to
be actioned.

Is this possible in VB?

Regards
John

Hi John,

This may not be perfect depending on exactly how you want your
criteria to behave, but it should give you a starting point:

'This is necessary to avoid having to unprotect
'and protect the sheet every time the macro fires
Private Sub Worksheet_Activate()
ActiveSheet.Protect UserInterfaceOnly:=True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim OldVal, CaseE As Boolean

If Target.Column = 4 Then
OldVal = Target.Offset(0, 100).Value
CaseE = IsEmpty(Target)
If Target = 999 Then
Target.Offset(0, 1).Locked = False
Else
Target.Offset(0, 6).Locked = False
End If
If (OldVal = 999 And Target < 999) Or CaseE Then
With Target.Offset(0, 1)
.ClearContents
.Locked = True
End With
End If
If Target < OldVal And Target = 999 Or CaseE Then
With Target.Offset(0, 6)
.Formula = Target.Offset(0, 7).Formula
.Locked = True
End With
End If
Target.Offset(0, 100) = Target.Value
End If
End Sub

It should just be a case of massaging the criteria and their order
until you get what you want. Note that I did have to use a 'helper'
cell at Column CZ to hold the 'old value' of the cell in column D,
since the worksheet doesn't have a 'before change' event. You can
certainly move this closer to your data... I was just playing it safe
by offsetting by 100.

Cheers,
Ivan.- Hide quoted text -

- Show quoted text -

Hi John,

Did you stick this bit in too?

'This is necessary to avoid having to unprotect
'and protect the sheet every time the macro fires
Private Sub Worksheet_Activate()
ActiveSheet.Protect UserInterfaceOnly:=True
End Sub

As it says in the comment, unless this bit is on the sheet as well,
the macro won't have permission to alter the locked cells in the
sheet. You can get around it by doing a 'Protect' / 'Unprotect', but I
prefer not to if I can avoid it.

If you have this Sub in and you're still having problems, let me know.
I didn't try your modified code, but at a glance it looked OK.

Cheers,
Ivan.