Help modify this code please

  • Thread starter Thread starter chimp
  • Start date Start date
C

chimp

someone kindly helped me to generate the following code, however i nee
it do a little extra to what it currently does.


PRIVATE SUB WORKSHEET_SELECTIONCHANGE(BYVAL TARGET AS EXCEL.RANGE)
IF TARGET.LOCKED = TRUE THEN

ACTIVESHEET.PROTECT PASSWORD:=\"YOURPASSWORD\"

IF TARGET.COLUMN = 1 AND TARGET.ROW <= 19 THEN
ON ERROR GOTO DUMP
ACTIVESHEET.UNPROTECT
END IF

END IF

DUMP:
IF ERR.NUMBER = 1004 THEN
MSGBOX \"WRONG PASSWORD\"
END IF

END SUB

i need the range to also include cells b12:b2

and

d5:d9

and

e1:e4

please help

And
 
Try

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim rngtest As Range

Set rngtest = Union([b2:b12], [d5:d9], [e1,e4])
If Target.Locked = True Then

ActiveSheet.Protect Password:="YourPassword"

If (Target.Column = 1 And Target.Row <= 19) Or Not
(Application.Intersect(Target, rngtest) Is Nothing) Then
On Error GoTo Dump
ActiveSheet.Unprotect
End If

End If

Dump:
If Err.Number = 1004 Then
MsgBox "Wrong Password"
End If

End Sub
 
I think this might work -
Good luck
jr


Private Sub WORKSHEET_SELECTIONCHANGE(ByVal Target As
Excel.Range)
If Target.Locked = True Then
ActiveSheet.Protect PASSWORD:="Yourpassword"

If (Target.Column = 1 And Target.Row <= 19) _
Or IsIn(Target, Range("d5: d9")) _
Or IsIn(Target, Range("b12:b2")) _
Or IsIn(Target, Range("e1: e4")) Then
On Error GoTo DUMP
ActiveSheet.Unprotect
End If

End If

DUMP:
If Err.Number = 1004 Then
MsgBox "WRONG PASSWORD"
End If

End Sub

Function IsIn(Target As Range, Test As Range)
IsIn = False
For Each c In Test
If Target.Address = c.Address Then IsIn = True
Next c
End Function
 
Replace your

IF TARGET.COLUMN...

line with

If Not Intersect(Target, _
Range("A1:A19,B2:B12,D5:D9,E1:E4")) Is Nothing Then
 
Back
Top