Can Worksheet_Change call itself?

[Brad E.]

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?

 

[Mike H]

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.]

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

 

[exceluserforeman]

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.]

It works if I don't disable the "EnableEvents" at the beginning of the
Worksheet_Change code.

 

[Brad E.]

Actually, I will want to Re-enable events prior to changing the cell.

 

[exceluserforeman]

Sorry about the diatribe in my first reply.

Why do you not just implement a userform.

The useform could enter the data into the worksheet. If the selected
quantity is not
=<9 then you can hide the check boxes or renove the quantities. It will be
easier to use and restrict the user frm inputting the wrong values in the
wrong places.