Event Procedure - Extending code

Hi all,

I was very kindly helped out (by Tom Ogilvy) to create an event procedure
code that locks a cell on a worksheet as soon as something is entered into
that cell. The code itself is as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo ErrHandler:
If Target.Address = "$B$8" Then
Application.EnableEvents = False
Me.Unprotect
If Target.Value = "Null" Then
Range("B8").Locked = False
Else
Range("B8").Locked = True
End If
Me.Protect
Application.EnableEvents = True
End If
ErrHandler:
Application.EnableEvents = True
If Err.Number <> 0 Then
End If
End Sub

However, I am now trying to to get the code to look at more than just one
cell, and have been struggling with this for the last day! Does anyone know
if it will be possible to do this using the code above, and if so, how would
I go about it? I have tried repeating the if loops etc. but with no luck!

Thanks in advance for any help!!

TBD

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng as Range
On Error GoTo ErrHandler:
set rng = Range("B8,C9,F12,M13,O3")
If Not intersect(target,rng) is nothing then
Application.EnableEvents = False
Me.Unprotect
If Target.Value = "Null" Then
Target.Locked = False
Else
Target.Locked = True
End If
Me.Protect
Application.EnableEvents = True
End If
ErrHandler:
Application.EnableEvents = True
End Sub

if you checking to see if the cell is empty use

if isemtpy(Target) then

Otherwise you are checking if the string "Null" is entered in the cell
(which may be what you want, I don't know). that said, if the cell was
unlocked and the person entered something, then the code would lock the
cell. So I am not sure how the user would be able to clear it - but then
you know what you are about and I don't.

--
Regards,
Tom Ogilvy


"TBD" <TBD@discussions.microsoft.com> wrote in message
news:637084C4-F20F-43AA-BA8D-DA4D5B07CC87@microsoft.com...
> Hi all,
>
> I was very kindly helped out (by Tom Ogilvy) to create an event procedure
> code that locks a cell on a worksheet as soon as something is entered into
> that cell. The code itself is as follows:
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> On Error GoTo ErrHandler:
> If Target.Address = "$B$8" Then
> Application.EnableEvents = False
> Me.Unprotect
> If Target.Value = "Null" Then
> Range("B8").Locked = False
> Else
> Range("B8").Locked = True
> End If
> Me.Protect
> Application.EnableEvents = True
> End If
> ErrHandler:
> Application.EnableEvents = True
> If Err.Number <> 0 Then
> End If
> End Sub
>
> However, I am now trying to to get the code to look at more than just one
> cell, and have been struggling with this for the last day! Does anyone
know
> if it will be possible to do this using the code above, and if so, how
would
> I go about it? I have tried repeating the if loops etc. but with no luck!
>
> Thanks in advance for any help!!
>
> TBD

Tom, once again, thank you for your help!! That has worked perfectly, and
was exactly what I was looking for.

Regards,
TBD

"Tom Ogilvy" wrote:

> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> Dim rng as Range
> On Error GoTo ErrHandler:
> set rng = Range("B8,C9,F12,M13,O3")
> If Not intersect(target,rng) is nothing then
> Application.EnableEvents = False
> Me.Unprotect
> If Target.Value = "Null" Then
> Target.Locked = False
> Else
> Target.Locked = True
> End If
> Me.Protect
> Application.EnableEvents = True
> End If
> ErrHandler:
> Application.EnableEvents = True
> End Sub
>
> if you checking to see if the cell is empty use
>
> if isemtpy(Target) then
>
> Otherwise you are checking if the string "Null" is entered in the cell
> (which may be what you want, I don't know). that said, if the cell was
> unlocked and the person entered something, then the code would lock the
> cell. So I am not sure how the user would be able to clear it - but then
> you know what you are about and I don't.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "TBD" <TBD@discussions.microsoft.com> wrote in message
> news:637084C4-F20F-43AA-BA8D-DA4D5B07CC87@microsoft.com...
> > Hi all,
> >
> > I was very kindly helped out (by Tom Ogilvy) to create an event procedure
> > code that locks a cell on a worksheet as soon as something is entered into
> > that cell. The code itself is as follows:
> >
> > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> > On Error GoTo ErrHandler:
> > If Target.Address = "$B$8" Then
> > Application.EnableEvents = False
> > Me.Unprotect
> > If Target.Value = "Null" Then
> > Range("B8").Locked = False
> > Else
> > Range("B8").Locked = True
> > End If
> > Me.Protect
> > Application.EnableEvents = True
> > End If
> > ErrHandler:
> > Application.EnableEvents = True
> > If Err.Number <> 0 Then
> > End If
> > End Sub
> >
> > However, I am now trying to to get the code to look at more than just one
> > cell, and have been struggling with this for the last day! Does anyone
> know
> > if it will be possible to do this using the code above, and if so, how
> would
> > I go about it? I have tried repeating the if loops etc. but with no luck!
> >
> > Thanks in advance for any help!!
> >
> > TBD
>
>
>