Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete creates error during Change event
Why would deleting the values of a range of cells cause an error
during a simple Worksheet_Change event? sample that causes error: If Target.Value = 1500 Then Application.ScreenUpdating = False For i = 1 To 1000 Target.Offset(i, 0).Select Selection.Value = i Next End If Still very new to Excel VBA, so any help is greatly appreciated. Thanks, Random |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete creates error during Change event
Random,
What error are you getting? You should disable events if you are changing cell values in the _Change event. If you don't, the change event changes a value, which calls the Change event, which changes a value, which calls the Change event, and so on and so on. Your code should look like Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False ' ' rest of code ' Application.EnableEvents = True End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "Random" <Random@nwhere wrote in message ... Why would deleting the values of a range of cells cause an error during a simple Worksheet_Change event? sample that causes error: If Target.Value = 1500 Then Application.ScreenUpdating = False For i = 1 To 1000 Target.Offset(i, 0).Select Selection.Value = i Next End If Still very new to Excel VBA, so any help is greatly appreciated. Thanks, Random |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete creates error during Change event
Chip,
Your response answers another question I was going to ask... how to stop the event from occuring on changes caused by the Change event. The error that I am getting is --------------------------- Run-time error '13': Type mismatch ---------------------------- Could this possibly be due to deleting more than one cell at a time and the target not really being a cell, but a range? Random On Wed, 27 Aug 2003 20:21:09 -0500, "Chip Pearson" wrote: Random, What error are you getting? You should disable events if you are changing cell values in the _Change event. If you don't, the change event changes a value, which calls the Change event, which changes a value, which calls the Change event, and so on and so on. Your code should look like Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False ' ' rest of code ' Application.EnableEvents = True End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete creates error during Change event
Yes, if Target is more than 1 cell, then
If Target.Value = 1500 then will cause a type mismatch error If Target.count 1 then exit sub If Target.Value = 1500 Then Application.ScreenUpdating = False For i = 1 To 1000 Target.Offset(i, 0).Select Selection.Value = i Next End If -- Regards, Tom Ogilvy Random <Random@nwhere wrote in message ... Chip, Your response answers another question I was going to ask... how to stop the event from occuring on changes caused by the Change event. The error that I am getting is --------------------------- Run-time error '13': Type mismatch ---------------------------- Could this possibly be due to deleting more than one cell at a time and the target not really being a cell, but a range? Random On Wed, 27 Aug 2003 20:21:09 -0500, "Chip Pearson" wrote: Random, What error are you getting? You should disable events if you are changing cell values in the _Change event. If you don't, the change event changes a value, which calls the Change event, which changes a value, which calls the Change event, and so on and so on. Your code should look like Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False ' ' rest of code ' Application.EnableEvents = True End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete creates error during Change event
Hi Random
Like what Tom has mentioned, the error happens because the target has more than 1 cells. Hence, the mismatched error will occur whenever a change is made to a range of cells eg. copying a range of values to the worksheet. In the previous code provided by Tom, VBA will step out of the worksheet_change event when you are dealing with more than one cell change. In other words, if you tried copying a range of cells onto the worksheet and even though one of copied values is 1500, your written code to populate the next 1000 entries will not be run. You may also consider : Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Dim i% Dim cell As Object For Each cell In Target If cell.Value = 1500 Then Application.ScreenUpdating = False For i = 1 To 1000 Target.Offset(i, 0).Select Selection.Value = i Next End If Next cell Application.EnableEvents = True End Sub This option will iterate all the cells in the target range and check whether if any of the cell value equates to 1500. Hopefully, the above info can be of help to ya. ^_^ Regards Jo Lee ------------------------------------------------------------------------------ "Tom Ogilvy" wrote in message ... Yes, if Target is more than 1 cell, then If Target.Value = 1500 then will cause a type mismatch error If Target.count 1 then exit sub If Target.Value = 1500 Then Application.ScreenUpdating = False For i = 1 To 1000 Target.Offset(i, 0).Select Selection.Value = i Next End If -- Regards, Tom Ogilvy Random <Random@nwhere wrote in message ... Chip, Your response answers another question I was going to ask... how to stop the event from occuring on changes caused by the Change event. The error that I am getting is --------------------------- Run-time error '13': Type mismatch ---------------------------- Could this possibly be due to deleting more than one cell at a time and the target not really being a cell, but a range? Random On Wed, 27 Aug 2003 20:21:09 -0500, "Chip Pearson" wrote: Random, What error are you getting? You should disable events if you are changing cell values in the _Change event. If you don't, the change event changes a value, which calls the Change event, which changes a value, which calls the Change event, and so on and so on. Your code should look like Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False ' ' rest of code ' Application.EnableEvents = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Addition of error bars creates black filled in areas on chart in 2 | Charts and Charting in Excel | |||
Spacebar creates value error | Excel Discussion (Misc queries) | |||
VLOOKUP post UDF (#DigitsFirstID) creates error | Excel Worksheet Functions | |||
Opening excel creates an error message or opens Book 1 | Setting up and Configuration of Excel | |||
Combobox creates error | Excel Worksheet Functions |