Q for Ivyleaf

J

johnsail

Hi Ivan
last week you provided me a routine for unlocking cells:

'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, 8).Value
CaseE = IsEmpty(Target)
If Target = "Mileage" Or Target = "mileage" Then
Target.Offset(0, 1).Locked = False
Target.Offset(0, 1).Select
Else
Target.Offset(0, 6).Locked = False
Target.Offset(0, 6).Select
End If
If (OldVal = "Mileage" And Target <> "Mileage") Or CaseE Then
With Target.Offset(0, 1)
.ClearContents
.Locked = True
End With
With Target.Offset(0, 6)
.ClearContents
.Select
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
Target.Offset(0, 1).Select
End If
Target.Offset(0, 8) = Target.Value
End If
End Sub

As you can see I have been working on it and all works well EXCEPT for:
a) I can't seem to get the sheet Password Protected
Activating the code by switching sheets does protect the sheet - but without
a password. Protecting the sheet with a password causes the code to fall over
and my attemps to ActiveSheet.Unprotect/Protect within the rtoutine have all
failed.
Q is there a way of applying a password?

b) You will see that I have had to include "mileage" as well as "Mileage" in
the test - Excel validation allows both even though a drop-down list is being
used. This is working OK (even preferred) in the data entry part of the
routine but I can't seem to get the correct syntax to make the correct/delete
part of the routine working.
Q Can you help?

Regards
John
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top