Using Line Numbers is definitely "old school" stuff ie Excel 95. Most of it
is rubbish.
Goto 999
999
......
Activesheet.CircleInvalid
What?
.................
In fact the whole thing is rubbish and poorly constructed.
You should implement the "If Then End if" construct. And maybe "Select
Case". I am surprised you even got it to work at all.
To get a better response, you would better to explain the whole construct
because reading the code is just bullsh*t. Break the query up into four
questions to get the best results. No one is going to rewrite this for free.
The original question was the best.
Brad E." wrote:
> Here is the portion I am trying to get working for me. In the middle of the
> 50 code is the line to change some of B16:J16. When those cell(s) change, I
> want this same code to run with (E16) as the new target, and it would GoTo 70
> to run code. After execution of the E16 target, it would come back to the
> middle of the 50 code with B3 still as the target....you know, calling itself
> and returning.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Application.EnableEvents = False
> ActiveSheet.ClearCircles
> Application.ScreenUpdating = False
>
> 10 If Intersect(Target, Range("A1")) Is Nothing Then GoTo 20
> 20 If Intersect(Target, Range("C2")) Is Nothing Then GoTo 30
> 30 if Intersect(Target, Range("F2")) Is Nothing Then GoTo 40
> 40 If Intersect(Target, Range("A3")) Is Nothing And Intersect(Target,
> Range("E4")) Is Nothing Then GoTo 50
> 50 If Intersect(Target, Range("B3")) Is Nothing Then GoTo 60
> OLDCOUNT = Range("C3").Value
> If (Target + 0) = OLDCOUNT Then GoTo 999
> 'LOCK UNUSED CELLS
> ActiveSheet.Range("B15:J31").Locked = False
> ActiveSheet.Range("B43:J57").Locked = False
> If (Target + 0) < 9 Then
> X = Mid("CDEFGHIJ", Target, 1)
> ActiveSheet.Range(X & "15:J31").Locked = True
> ActiveSheet.Range(X & "43:J57").Locked = True
> End If
> 'MAKE CHECK BOXES AVAILABLE IF NEW COUNT > OLD COUNT
> If (Target + 0) > OLDCOUNT Then
> For X = (OLDCOUNT * 3 + 1) To (Target * 3)
> ActiveSheet.Shapes(X).Visible = True
> Next X
> For X = (OLDCOUNT + 1) To Target 'Implements a Worksheet_Change
> event on Type.
> Range("A16").Offset(0, X).Value = Range("A16").Offset(0, X).Value
> Next X
> End If
> 'MAKE CHECK BOXES UNAVAILABLE IF NEW COUNT < OLD COUNT
> If (Target + 0) < OLDCOUNT Then
> For X = (Target * 3 + 1) To (OLDCOUNT * 3)
> ActiveSheet.Shapes(X).Visible = False
> Next X
> End If
> Range("C3").Value = Target + 0
> GoTo 999
> 60 If Intersect(Target, Range("B15:J15")) Is Nothing Then GoTo 70
> 70 If Intersect(Target, Range("B16:J16")) Is Nothing Then GoTo 80
> Target = UCase(Target)
> TU = (Target = "T" Or Target = "U")
> ATU = (Target = "A" Or TU)
> AMTU = (Target = "M" Or ATU)
> ACMTU = (Target = "C" Or AMTU)
> With Target
> If AMTU Or .Offset(-1, 0).Value < 1998 Then Range(.Offset(3, 0),
> .Offset(4, 0)).Locked = True _
> Else Range(.Offset(3, 0),
> .Offset(4, 0)).Locked = False
> If ACMTU Then
> Range(.Offset(5, 0), .Offset(9, 0)).Locked = True
> For X = ((.Column * 3) - 5) To ((.Column * 3) - 3)
> ActiveSheet.Shapes(X).Visible = False
> Next X
> Else
> Range(.Offset(5, 0), .Offset(9, 0)).Locked = False
> For X = ((.Column * 3) - 5) To ((.Column * 3) - 3)
> ActiveSheet.Shapes(X).Visible = True
> Next X
> End If
> If ATU Or (.Offset(-1, 0).Value < 1976 And .Offset(2, 0).Value = 7)
> Or _
> (.Offset(-1, 0).Value > 1989 And .Offset(-1, 0).Value < 2011
> And .Offset(2, 0).Value = 27) Or _
> (.Offset(-1, 0).Value > 2010 And .Offset(2, 0).Value = 98)
> Then
> .Offset(10, 0).Locked = False
> Else
> .Offset(10, 0).Locked = True
> End If
> If TU Then
> Range(.Offset(11, 0), .Offset(14, 0)).Locked = True
> Else
> Range(.Offset(11, 0), .Offset(14, 0)).Locked = False
> End If
> End With
> GoTo 999
> 999 Application.ScreenUpdating = True
> ActiveSheet.CircleInvalid
> Application.EnableEvents = True
> End Sub
>
> --
> Brad E.
> ----------------------------------------------
>
> "Mike H" wrote:
>
> > Brad,
> >
> > You'l l see what's wrong with that approach if you run this snippet of code
> > by changing a cell on the worksheet,
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > For X = 4 To 5
> > Range("A16").Offset(0, X).Value = Range("A16").Offset(0, X).Value
> > MsgBox Target.Address
> > Next
> > End Sub
> >
> > As you will note you will go into a recursive loop. Hit CTRL+Break. There
> > will be a way to do what you want but I doubt this is it. Post your code.
> >
> > Mike
> >
> > "Brad E." wrote:
> >
> > > You've got it correct. I am just trying to set the value so that
> > > Worksheet_Change will be called with E16 the target and then again with F16
> > > the target.
> > >
> > > For each of the vehicles 4 and 5 (because we are increasing from the old
> > > entry of "3"), I want to run further code which is already programmed in the
> > > Worksheet_Change event, with B16:J16 the intersect.
> > > --
> > > Brad E.
> > > -----------------------------------------------
> > > "Mike H" wrote:
> > >
> > > > Hi,
> > > >
> > > > I think you need to post your code becuse this bit doesn't seem to make sense
> > > >
> > > > In a For loop (For X = 4 to 5, in this case), I have
> > > > Range("A16").Offset(0, X).Value = Range("A16").Offset(0, X).Value
> > > >
> > > > It does nothing because for any value of X in the loop it becomes
> > > >
> > > > Range("A16").Offset(0, 4).Value = Range("A16").Offset(0, 4).Value
> > > >
> > > > in other words leave the value of that cell the same
> > > >
> > > > Mike
> > > >
> > > >
> > > > "Brad E." wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > In my Worksheet_Change event, I would like to change a cell and call the
> > > > > Worksheet_Change event again.
> > > > >
> > > > > In B3, the user enters a number up to 9, to access up to 9 vehicles. If the
> > > > > old value was 3 and the user enters 5, then the Worksheet_Change acknowledges
> > > > > the change and Displays some check boxes (Visible = True) for the 4th and 5th
> > > > > column. The check boxes were hidden when B3 originally went smaller than 5
> > > > > (Visible = False). The ability to use these check boxes also depends on the
> > > > > entry in row 16. So first, I make the check boxes for vehicles 4 and 5
> > > > > visible, and then want to re-enter the entry in row 16 for these 2 vehicles.
> > > > > I thought if I just set a cell to a value, it would initiate the
> > > > > Worksheet_Change event and the new Target would be the cell in row 16.
> > > > >
> > > > > In a For loop (For X = 4 to 5, in this case), I have
> > > > > Range("A16").Offset(0, X).Value = Range("A16").Offset(0, X).Value
> > > > >
> > > > > Part of my Worksheet_Change event includes an intersection of Target and
> > > > > "B16:J16", however the code after this intersection is not running.
> > > > >
> > > > > What are my options to be able to run the code?
> > > > > --
> > > > > Thanks, Brad E.
Similar Threads
