Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Q for Ivyleaf
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|