Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
debug message pops up when I select a range of cells and delete
Hello,
When i select a range of cells and hit delete, a that debug message appears. The range in question is controlled by the following sytax in VB: If target.Row < 10 Or target.Row 13 Then Exit Sub Application.EnableEvents = False If target.Column = 8 Then If IsNumeric(target) = False Then If Len(target) 0 Then target.Offset(0, -2) = "" End If End If End If Application.EnableEvents = True End Sub i have to sometimes select this range of cells all at once and delete, but how can I prevent the debug message from appearing. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
debug message pops up when I select a range of cells and delete
What does the message say?
-- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Brian" wrote in message ... Hello, When i select a range of cells and hit delete, a that debug message appears. The range in question is controlled by the following sytax in VB: If target.Row < 10 Or target.Row 13 Then Exit Sub Application.EnableEvents = False If target.Column = 8 Then If IsNumeric(target) = False Then If Len(target) 0 Then target.Offset(0, -2) = "" End If End If End If Application.EnableEvents = True End Sub i have to sometimes select this range of cells all at once and delete, but how can I prevent the debug message from appearing. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
debug message pops up when I select a range of cells and delet
run time error 13
type mismatch "Chip Pearson" wrote: What does the message say? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Brian" wrote in message ... Hello, When i select a range of cells and hit delete, a that debug message appears. The range in question is controlled by the following sytax in VB: If target.Row < 10 Or target.Row 13 Then Exit Sub Application.EnableEvents = False If target.Column = 8 Then If IsNumeric(target) = False Then If Len(target) 0 Then target.Offset(0, -2) = "" End If End If End If Application.EnableEvents = True End Sub i have to sometimes select this range of cells all at once and delete, but how can I prevent the debug message from appearing. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
debug message pops up when I select a range of cells and delet
I suspect an error in a cell.
As an experiment, I entered =1/0 into a cell to give me #DIV/0 Then tried MsgBox Len(Selection) This gave me Run-time Error 13 You can check for an error: iserror(selection) Keep in mind your Target may span many cells, not just one. -- Rob van Gelder - http://www.vangelder.co.nz/excel "Brian" wrote in message ... run time error 13 type mismatch "Chip Pearson" wrote: What does the message say? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Brian" wrote in message ... Hello, When i select a range of cells and hit delete, a that debug message appears. The range in question is controlled by the following sytax in VB: If target.Row < 10 Or target.Row 13 Then Exit Sub Application.EnableEvents = False If target.Column = 8 Then If IsNumeric(target) = False Then If Len(target) 0 Then target.Offset(0, -2) = "" End If End If End If Application.EnableEvents = True End Sub i have to sometimes select this range of cells all at once and delete, but how can I prevent the debug message from appearing. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
debug message pops up when I select a range of cells and delet
OP doesnt check that Target.Count =1 if the target contains multiple cells: Len(target) will produce runtime error. whereas len(target.cells(1)) would not. normally i'd include if Target.Count < 1 then exit sub -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Rob van Gelder wrote : I suspect an error in a cell. As an experiment, I entered =1/0 into a cell to give me #DIV/0 Then tried MsgBox Len(Selection) This gave me Run-time Error 13 You can check for an error: iserror(selection) Keep in mind your Target may span many cells, not just one. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
debug message pops up when I select a range of cells and delete
This line:
If Len(Target) 0 Then is the same as: If Len(Target.value) 0 Then But with multiple cells, target doesn't have a value. So what should happen when you clean up a multicell range? One easy solution is to add a check for multiple cells at the top: if target.cells.count 1 then exit sub or you could loop through the cells that are in that area that you're concerned with: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range Dim myCell As Range Set myRng = Me.Range("H10:H12") If Intersect(Target, myRng) Is Nothing Then Exit Sub Application.EnableEvents = False For Each myCell In Intersect(Target, myRng).Cells If IsError(myCell.Value) Then 'do nothing?? ElseIf IsNumeric(myCell.Value) = False Then If Len(myCell.Value) 2 Then myCell.Offset(0, -2).Value = "" End If End If Next myCell Application.EnableEvents = True End Sub Personally, I like checking the intersection to see if the change is happening in a range. I find it easier to read than checking the .row and .column. Brian wrote: Hello, When i select a range of cells and hit delete, a that debug message appears. The range in question is controlled by the following sytax in VB: If target.Row < 10 Or target.Row 13 Then Exit Sub Application.EnableEvents = False If target.Column = 8 Then If IsNumeric(target) = False Then If Len(target) 0 Then target.Offset(0, -2) = "" End If End If End If Application.EnableEvents = True End Sub i have to sometimes select this range of cells all at once and delete, but how can I prevent the debug message from appearing. -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
debug message pops up when I select a range of cells and delet
len(Range("B1:B2"))
produces type mismatch (len with a multi-cell argument). If you want to process multiple cells for each cell in Target If cell.Row < 10 Or cell.Row 13 Then Exit Sub Application.EnableEvents = False If cell.Column = 8 Then If IsNumeric(cell) = False Then If Len(cell) 0 Then cell.Offset(0, -2).ClearContents End If End If End If Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Rob van Gelder" wrote in message ... I suspect an error in a cell. As an experiment, I entered =1/0 into a cell to give me #DIV/0 Then tried MsgBox Len(Selection) This gave me Run-time Error 13 You can check for an error: iserror(selection) Keep in mind your Target may span many cells, not just one. -- Rob van Gelder - http://www.vangelder.co.nz/excel "Brian" wrote in message ... run time error 13 type mismatch "Chip Pearson" wrote: What does the message say? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Brian" wrote in message ... Hello, When i select a range of cells and hit delete, a that debug message appears. The range in question is controlled by the following sytax in VB: If target.Row < 10 Or target.Row 13 Then Exit Sub Application.EnableEvents = False If target.Column = 8 Then If IsNumeric(target) = False Then If Len(target) 0 Then target.Offset(0, -2) = "" End If End If End If Application.EnableEvents = True End Sub i have to sometimes select this range of cells all at once and delete, but how can I prevent the debug message from appearing. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I'm trying to open an Excel file but a browsing message pops up | Excel Discussion (Misc queries) | |||
How to disable the message that pops up on hyperlinks in XL | Excel Discussion (Misc queries) | |||
Is It Possible To Have... After A Button Press A Message Pops Up ? | Excel Worksheet Functions | |||
EVEN AFTER PUTTING CODE IN VB, THE MESSAGE 'WORKBOOK CONTAINS LINK' POPS UP- HOW TO GET OVER THIS | Excel Discussion (Misc queries) | |||
How do I select a whole range and delete the contents of the cells? | Excel Programming |