Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stepping through Code
Have a code that upon a worksheet_change event creates an AutoShape object
and inserts onto the Worksheet at a specific Location. The problem is the code ONLY works in Step-Mode, IF I break before the object is created and then step through it. If I let the code run without breaking it always returns an 1004 - application or object defined error, when it attempts to create the AutoShape. Why would it work in Step-Mode and not in Normal RunTime? Nothing else changes, no code, no procedures, no events, no workbooks. Ben -- When you lose your mind, you free your life. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stepping through Code
Sounds like you are doing something on a sheet other than the sheet where
the change event occured. If this is the case, unqualified references such as Range("B9").Select would refer to the worksheet that contains the code, not the active sheet if that were a different sheet. In that type of situation, you would be intending to select on the activesheet, but the code thinks you want to select on the worksheet that contains the code. You can't select on a sheet unless it is the activesheet. So for example In the Sheet2 code module code like Private Sub Worksheet_Change(ByVal Target As Range) worksheets("Sheet1").Activate Range("B9").Select '<== 1004 error here End Sub better (don't select, but) Private Sub Worksheet_Change(ByVal Target As Range) with worksheets("Sheet1") .Activate .Range("B9").Select End with End Sub If this isn't exactly what you are doing, I will bet it is close and the source of your problem. -- Regards, Tom Ogilvy "ben" (remove this if mailing direct) wrote in message ... Have a code that upon a worksheet_change event creates an AutoShape object and inserts onto the Worksheet at a specific Location. The problem is the code ONLY works in Step-Mode, IF I break before the object is created and then step through it. If I let the code run without breaking it always returns an 1004 - application or object defined error, when it attempts to create the AutoShape. Why would it work in Step-Mode and not in Normal RunTime? Nothing else changes, no code, no procedures, no events, no workbooks. Ben -- When you lose your mind, you free your life. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stepping through Code
there is only one sheet in the workbook being referenced, and this code is
running from an add-on where ALL references are qualified the actuall line that errors out is Set sh = wks.Shapes.AddShape(msoShapeDownArrow, 354.75, 162 + (RowN * 21), 24, 30) sh is a public shared variable referring to a Shape and wks is a worksheet object that is passed into the sub and is the same whether stepping through or at Run Time Sub AddShape(RowN As Integer, wks As Worksheet) -- When you lose your mind, you free your life. "Tom Ogilvy" wrote: Sounds like you are doing something on a sheet other than the sheet where the change event occured. If this is the case, unqualified references such as Range("B9").Select would refer to the worksheet that contains the code, not the active sheet if that were a different sheet. In that type of situation, you would be intending to select on the activesheet, but the code thinks you want to select on the worksheet that contains the code. You can't select on a sheet unless it is the activesheet. So for example In the Sheet2 code module code like Private Sub Worksheet_Change(ByVal Target As Range) worksheets("Sheet1").Activate Range("B9").Select '<== 1004 error here End Sub better (don't select, but) Private Sub Worksheet_Change(ByVal Target As Range) with worksheets("Sheet1") .Activate .Range("B9").Select End with End Sub If this isn't exactly what you are doing, I will bet it is close and the source of your problem. -- Regards, Tom Ogilvy "ben" (remove this if mailing direct) wrote in message ... Have a code that upon a worksheet_change event creates an AutoShape object and inserts onto the Worksheet at a specific Location. The problem is the code ONLY works in Step-Mode, IF I break before the object is created and then step through it. If I let the code run without breaking it always returns an 1004 - application or object defined error, when it attempts to create the AutoShape. Why would it work in Step-Mode and not in Normal RunTime? Nothing else changes, no code, no procedures, no events, no workbooks. Ben -- When you lose your mind, you free your life. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stepping through Code
I can get that error if the sheet is protected. Otherwise, it worked for
me. -- Regards, Tom Ogilvy "ben" (remove this if mailing direct) wrote in message ... there is only one sheet in the workbook being referenced, and this code is running from an add-on where ALL references are qualified the actuall line that errors out is Set sh = wks.Shapes.AddShape(msoShapeDownArrow, 354.75, 162 + (RowN * 21), 24, 30) sh is a public shared variable referring to a Shape and wks is a worksheet object that is passed into the sub and is the same whether stepping through or at Run Time Sub AddShape(RowN As Integer, wks As Worksheet) -- When you lose your mind, you free your life. "Tom Ogilvy" wrote: Sounds like you are doing something on a sheet other than the sheet where the change event occured. If this is the case, unqualified references such as Range("B9").Select would refer to the worksheet that contains the code, not the active sheet if that were a different sheet. In that type of situation, you would be intending to select on the activesheet, but the code thinks you want to select on the worksheet that contains the code. You can't select on a sheet unless it is the activesheet. So for example In the Sheet2 code module code like Private Sub Worksheet_Change(ByVal Target As Range) worksheets("Sheet1").Activate Range("B9").Select '<== 1004 error here End Sub better (don't select, but) Private Sub Worksheet_Change(ByVal Target As Range) with worksheets("Sheet1") .Activate .Range("B9").Select End with End Sub If this isn't exactly what you are doing, I will bet it is close and the source of your problem. -- Regards, Tom Ogilvy "ben" (remove this if mailing direct) wrote in message ... Have a code that upon a worksheet_change event creates an AutoShape object and inserts onto the Worksheet at a specific Location. The problem is the code ONLY works in Step-Mode, IF I break before the object is created and then step through it. If I let the code run without breaking it always returns an 1004 - application or object defined error, when it attempts to create the AutoShape. Why would it work in Step-Mode and not in Normal RunTime? Nothing else changes, no code, no procedures, no events, no workbooks. Ben -- When you lose your mind, you free your life. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stepping through Code
that's the wierd thing, i don't get it on a fresh worksheet, only running on
this worksheet and only if i try to do it through the worksheet change event, there's an object reference somewhere screwing up but now when it goes through step mode. Totally Lost and confused. well i'll just try different stuff till it works -- When you lose your mind, you free your life. "Tom Ogilvy" wrote: I can get that error if the sheet is protected. Otherwise, it worked for me. -- Regards, Tom Ogilvy "ben" (remove this if mailing direct) wrote in message ... there is only one sheet in the workbook being referenced, and this code is running from an add-on where ALL references are qualified the actuall line that errors out is Set sh = wks.Shapes.AddShape(msoShapeDownArrow, 354.75, 162 + (RowN * 21), 24, 30) sh is a public shared variable referring to a Shape and wks is a worksheet object that is passed into the sub and is the same whether stepping through or at Run Time Sub AddShape(RowN As Integer, wks As Worksheet) -- When you lose your mind, you free your life. "Tom Ogilvy" wrote: Sounds like you are doing something on a sheet other than the sheet where the change event occured. If this is the case, unqualified references such as Range("B9").Select would refer to the worksheet that contains the code, not the active sheet if that were a different sheet. In that type of situation, you would be intending to select on the activesheet, but the code thinks you want to select on the worksheet that contains the code. You can't select on a sheet unless it is the activesheet. So for example In the Sheet2 code module code like Private Sub Worksheet_Change(ByVal Target As Range) worksheets("Sheet1").Activate Range("B9").Select '<== 1004 error here End Sub better (don't select, but) Private Sub Worksheet_Change(ByVal Target As Range) with worksheets("Sheet1") .Activate .Range("B9").Select End with End Sub If this isn't exactly what you are doing, I will bet it is close and the source of your problem. -- Regards, Tom Ogilvy "ben" (remove this if mailing direct) wrote in message ... Have a code that upon a worksheet_change event creates an AutoShape object and inserts onto the Worksheet at a specific Location. The problem is the code ONLY works in Step-Mode, IF I break before the object is created and then step through it. If I let the code run without breaking it always returns an 1004 - application or object defined error, when it attempts to create the AutoShape. Why would it work in Step-Mode and not in Normal RunTime? Nothing else changes, no code, no procedures, no events, no workbooks. Ben -- When you lose your mind, you free your life. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stepping through Code
I've had this type of problem a number of times when working with
AutoShapes - works in step-through, but throws an error when running normally. The tricks I've resorted to a 1. Prior to the statement, add a line: If wks Is Nothing Then MsgBox "blah blah blah" Exit Sub End If 2. Add an "On Error Resume Next" prior to the statement, then check the error number after: If Err.Number < 0 Then MsgBox "blah blah blah" Exit Sub Else On Error Goto Err_Handler End If 3. Check the existence of the shape after the AddShape method: If sh Is Nothing Then MsgBox "blah blah blah" Exit Sub End If 4. Select something before or after the operation - a range or shape - depending upon what you're doing. Strangely, adding these items often makes the errors go away, although you really haven't done anything other than check for the existence of objects or errors. It's as though it gives Excel a "chance to catch up". HTH, Nick Hebb |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stepping through Code
that is exceedingly unusual, adding a Range.select statement cleared up the
problem, sounds like this may be a BUG. Thanks for the workaround Nick -- When you lose your mind, you free your life. "Nick Hebb" wrote: I've had this type of problem a number of times when working with AutoShapes - works in step-through, but throws an error when running normally. The tricks I've resorted to a 1. Prior to the statement, add a line: If wks Is Nothing Then MsgBox "blah blah blah" Exit Sub End If 2. Add an "On Error Resume Next" prior to the statement, then check the error number after: If Err.Number < 0 Then MsgBox "blah blah blah" Exit Sub Else On Error Goto Err_Handler End If 3. Check the existence of the shape after the AddShape method: If sh Is Nothing Then MsgBox "blah blah blah" Exit Sub End If 4. Select something before or after the operation - a range or shape - depending upon what you're doing. Strangely, adding these items often makes the errors go away, although you really haven't done anything other than check for the existence of objects or errors. It's as though it gives Excel a "chance to catch up". HTH, Nick Hebb |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stepping through Code
Yeah, I tried that trick on a lark after after reading about a similar
situation when copying from Excel to Word. In that case, you needed to change code from Range.PasteSpecial to Range.Select and Selection.PasteSpecial. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stepping through Code | Excel Discussion (Misc queries) | |||
How to get value of variable when single stepping through code? | Excel Programming | |||
VBA Code works by stepping through, not by running | Excel Programming | |||
Stepping through VBA code in Excel | Excel Programming | |||
Stepping through VBA code in Excel | Excel Programming |