Worksheet_Change macro locks up Excel
 

I have a worksheet on which there are data validation lists in
B7,B11,B15, and B23. When the user selects "Report" in any of these, I
want the cell in column D on the same row to return "N/A". Below is my
code. The problem is, when it executes the program gets locked in
calculation mode. Any suggestions?

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B7").Value = "Report" Then
Range("D7").Value = "N/A"

End If

If Range("B11").Value = "Report" Then
Range("D11").Value = "N/A"

End If

If Range("B15").Value = "Report" Then
Range("D15").Value = "N/A"

End If

If Range("B23").Value = "Report" Then
Range("D23").Value = "N/A"

End If




End Sub

Worksheet_Change macro locks up Excel
 

excel isn't freezing, it is continually running the code. Because with each
change of the "D" column, excel restarts the sub, which changes a cell, which
restarts the sub, etc.

To prevent this, insert exit sub commands in each if statement like below:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B7").Value = "Report" Then
Range("D7").Value = "N/A"
Exit Sub
End If

If Range("B11").Value = "Report" Then
Range("D11").Value = "N/A"
Exit Sub
End If

If Range("B15").Value = "Report" Then
Range("D15").Value = "N/A"
Exit Sub
End If

If Range("B23").Value = "Report" Then
Range("D23").Value = "N/A"
Exit Sub
End If
End Sub


"JG Scott" wrote:


I have a worksheet on which there are data validation lists in
B7,B11,B15, and B23. When the user selects "Report" in any of these, I
want the cell in column D on the same row to return "N/A". Below is my
code. The problem is, when it executes the program gets locked in
calculation mode. Any suggestions?

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B7").Value = "Report" Then
Range("D7").Value = "N/A"

End If

If Range("B11").Value = "Report" Then
Range("D11").Value = "N/A"

End If

If Range("B15").Value = "Report" Then
Range("D15").Value = "N/A"

End If

If Range("B23").Value = "Report" Then
Range("D23").Value = "N/A"

End If




End Sub

Worksheet_Change macro locks up Excel
 

Before you change a value, tell excel to stop looking:

application.enableevents = false
range("d11").value = "N/A"
application.enableevents = true

But it looks like you could do something like this:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("b7,b11,b15,b23")) Is Nothing Then
Exit Sub
End If

If LCase(Target.Value) = LCase("Report") Then
Application.EnableEvents = False
Target.Offset(0, 2).Value = "N/A"
Application.EnableEvents = True
End If
End Sub



JNW wrote:

excel isn't freezing, it is continually running the code. Because with each
change of the "D" column, excel restarts the sub, which changes a cell, which
restarts the sub, etc.

To prevent this, insert exit sub commands in each if statement like below:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B7").Value = "Report" Then
Range("D7").Value = "N/A"
Exit Sub
End If

If Range("B11").Value = "Report" Then
Range("D11").Value = "N/A"
Exit Sub
End If

If Range("B15").Value = "Report" Then
Range("D15").Value = "N/A"
Exit Sub
End If

If Range("B23").Value = "Report" Then
Range("D23").Value = "N/A"
Exit Sub
End If
End Sub

"JG Scott" wrote:


I have a worksheet on which there are data validation lists in
B7,B11,B15, and B23. When the user selects "Report" in any of these, I
want the cell in column D on the same row to return "N/A". Below is my
code. The problem is, when it executes the program gets locked in
calculation mode. Any suggestions?

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B7").Value = "Report" Then
Range("D7").Value = "N/A"

End If

If Range("B11").Value = "Report" Then
Range("D11").Value = "N/A"

End If

If Range("B15").Value = "Report" Then
Range("D15").Value = "N/A"

End If

If Range("B23").Value = "Report" Then
Range("D23").Value = "N/A"

End If




End Sub



--

Dave Peterson

Worksheet_Change macro locks up Excel
 

JG-

Use Dave's more concise method. The multiple if statements can get
confusing at times, and they really start to add up. Couldn't remember how
to do what he did myself.

JNW

"Dave Peterson" wrote:

Before you change a value, tell excel to stop looking:

application.enableevents = false
range("d11").value = "N/A"
application.enableevents = true

But it looks like you could do something like this:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("b7,b11,b15,b23")) Is Nothing Then
Exit Sub
End If

If LCase(Target.Value) = LCase("Report") Then
Application.EnableEvents = False
Target.Offset(0, 2).Value = "N/A"
Application.EnableEvents = True
End If
End Sub



JNW wrote:

excel isn't freezing, it is continually running the code. Because with each
change of the "D" column, excel restarts the sub, which changes a cell, which
restarts the sub, etc.

To prevent this, insert exit sub commands in each if statement like below:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B7").Value = "Report" Then
Range("D7").Value = "N/A"
Exit Sub
End If

If Range("B11").Value = "Report" Then
Range("D11").Value = "N/A"
Exit Sub
End If

If Range("B15").Value = "Report" Then
Range("D15").Value = "N/A"
Exit Sub
End If

If Range("B23").Value = "Report" Then
Range("D23").Value = "N/A"
Exit Sub
End If
End Sub

"JG Scott" wrote:


I have a worksheet on which there are data validation lists in
B7,B11,B15, and B23. When the user selects "Report" in any of these, I
want the cell in column D on the same row to return "N/A". Below is my
code. The problem is, when it executes the program gets locked in
calculation mode. Any suggestions?

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B7").Value = "Report" Then
Range("D7").Value = "N/A"

End If

If Range("B11").Value = "Report" Then
Range("D11").Value = "N/A"

End If

If Range("B15").Value = "Report" Then
Range("D15").Value = "N/A"

End If

If Range("B23").Value = "Report" Then
Range("D23").Value = "N/A"

End If




End Sub



--

Dave Peterson