Thread: 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.

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" <bw_rgb@hotmail.com(remove this if mailing direct)> wrote in message
news:B22D7BBD-5F6C-4237-9A44-46AF7A008DE4@microsoft.com...
> 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.

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" <bw_rgb@hotmail.com(remove this if mailing direct)> wrote in message
> news:B22D7BBD-5F6C-4237-9A44-46AF7A008DE4@microsoft.com...
> > 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.
>
>
>

I can get that error if the sheet is protected. Otherwise, it worked for
me.

--
Regards,
Tom Ogilvy


"ben" <bw_rgb@hotmail.com(remove this if mailing direct)> wrote in message
news:B453A197-4DEB-483E-960D-EC264A895E55@microsoft.com...
> 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" <bw_rgb@hotmail.com(remove this if mailing direct)> wrote in
message
> > news:B22D7BBD-5F6C-4237-9A44-46AF7A008DE4@microsoft.com...
> > > 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.
> >
> >
> >

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" <bw_rgb@hotmail.com(remove this if mailing direct)> wrote in message
> news:B453A197-4DEB-483E-960D-EC264A895E55@microsoft.com...
> > 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" <bw_rgb@hotmail.com(remove this if mailing direct)> wrote in
> message
> > > news:B22D7BBD-5F6C-4237-9A44-46AF7A008DE4@microsoft.com...
> > > > 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.
> > >
> > >
> > >
>
>
>

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 are:

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

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 are:
>
> 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
>
>

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.