Re: Can't get code to work when exiting cell

From: rob nobel (randwnobel_at_NOGOOBShotmail.comNOGOOBS)
Date: 02/14/04 

Date: Sat, 14 Feb 2004 16:49:29 +1030

Yes Dave, I actually figured out that that was the problem. But thanks for
suggesting it though. Thanks too for the If UCase(.Value) = "X", as I did
get it working but my bit was a bit long winded.
I also appreciate your "less typing" suggestion but I might stick with the
other as it seems to be a little easier to understand.

Rob

"Dave Peterson" <ec35720@msn.com> wrote in message
news:402D700B.3B7601DB@msn.com...
> If you want to use the cell you just changed, then change all those
activecell
> references to Target.
>
> Something like:
>
> Option Explicit
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> If Intersect(Target, Me.Range("N26:N1525")) Is Nothing Then
> GoTo nexttest
> Else
> GoTo BalCol
> End If
>
> 'Some other code here
>
> BalCol:
> With Target
> If UCase(.Value) = "X" Then
> .Offset(0, -7).Resize(1, 7).Locked = True
> .Offset(0, 2).Resize(1, 3).Locked = True
> ElseIf .Value = "" Then
> .Offset(0, -7).Resize(1, 7).Locked = False
> .Offset(0, 2).Resize(1, 3).Locked = False
> End If
> End With
>
> nexttest:
>
> End Sub
>
> But if you could live with an empty/not empty test, this is less typing:
>
> With Target
> .Offset(0, -7).Resize(1, 7).Locked = Not (CBool(IsEmpty(.Value)))
> .Offset(0, 2).Resize(1, 3).Locked = Not (CBool(IsEmpty(.Value)))
> End With
>
> rob nobel wrote:
> >
> > The part code (following), seems to work OK, but only if I click the
green
> > tick by the formula bar, as when I press enter, or select another cell
to
> > exit the cell where I've entered an X, it doesn't work.
> > I realise that this is because the active cell is no longer the one
where I
> > typed an X but I don't know how to get this procedure to do this.
> >
> > What I've tried to do (in an awkward way) is....
> > 1. Type an X into Column N and make all the cells (in that row) in
columns
> > G:N and P:R, locked.
> > 2. Make those cells unlocked if the x is deleted.
> > 3. What I'd also like is to have the procedure work whether it is an
upper
> > or lowercase x.
> > Can someone please help with this?
> >
> > Furthermore, is it possible to have separate Worksheet_Change events
for
> > different procedures on the same *** as I'm trying to do quite a
number of
> > things on the same *** and it all becomes rather confusing doing it
all
> > under the one Worksheet_Change event. ( I am also using a
> > Worksheet_SelectionChange event on this sheet.)
> >
> > Rob
> >
> > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> > If Intersect(Target, Me.Range("N26:N1525")) Is Nothing Then GoTo
> > NextTest Else GoTo BalCol
> >
> > 'Some other code here
> >
> > BalCol:
> > If ActiveCell = "X" Then
> > With ActiveCell
> > .Offset(0, -7).Resize(1, 7).Locked = True
> > .Offset(0, 2).Resize(1, 3).Locked = True
> > End With
> > End If
> > If ActiveCell = "" Then
> > With ActiveCell
> > .Offset(0, -7).Resize(1, 7).Locked = False
> > .Offset(0, 2).Resize(1, 3).Locked = False
> > End With
> > End If
> > End Sub
>
> --
>
> Dave Peterson
> ec35720@msn.com

Loading