| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Trigger Macro by Worksheet_Change | Excel Discussion (Misc queries) | |||
| Excel 2003 has wrong timing using the worksheet_change macro | Excel Worksheet Functions | |||
| calc locks up after running a macro that moves sheets to a new fil | Excel Discussion (Misc queries) | |||
| Macro locks me in a single XLS file | Excel Programming | |||
| Using Worksheet_Change to Auto Run a Macro | Excel Programming | |||
Linear Mode
