hello,

My VBA code sporadically hangs up with error "object-defined error" when I
try to add a new worksheet and draw shapes on it. Please see code below:

Option Explicit
'
Sub Sam()
Dim s As Worksheet

Set s = Sheets.Add

s.Select
ActiveSheet.Shapes.AddLine(10, 10, 250, 250).Select

End Sub
'
Sub bob()
Dim s As Worksheet

Set s = Sheets.Add

s.Shapes.AddLine(10, 10, 250, 250).Select

End Sub
'

When I open the workbook there is only a single worksheet. Routine "sam"
always runs fine, it always adds a new worksheet and draws the line. Routine
"bob" should do the same thing but it will give me the "object defined
error" when it is run the first time after file open; it does add the new
worksheet but then error results. After the error is dismissed "bob"
subsequently runs properly and without error...except that if all the newly
added sheets are manually deleted it again returns the error the first time
it is run.

The really weird thing is that "bob" will run fine if I step (F8) through it
under the same conditions that normally result in the error.

I don't understand why "bob" is inconsistent. I think I lifted the line
"s.Shapes.AddLine(10, 10, 250, 250).Select" from Excel VBA Help since it
seemed more efficient.

Anyone know why this line fails?

thanks!

Without testing, it looks like it's the .select that's giving you a problem.

You have to be on a sheet to select something on that sheet (range or object).
So if the sheet isn't the activesheet, you can't select stuff.

But "set s = sheets.add" should make that new worksheet the active sheet. (So
I'm confused here.)

But how about adding the line without selecting it:

Sub bob()
Dim s As Worksheet
Set s = Sheets.Add
s.Shapes.AddLine 10, 10, 250, 250
End Sub

Do you have the code in a general module or is it behind a worksheet or behind
ThisWorkbook?


Fred Smif wrote:

hello,

My VBA code sporadically hangs up with error "object-defined error" when I
try to add a new worksheet and draw shapes on it. Please see code below:

Option Explicit
'
Sub Sam()
Dim s As Worksheet

Set s = Sheets.Add

s.Select
ActiveSheet.Shapes.AddLine(10, 10, 250, 250).Select

End Sub
'
Sub bob()
Dim s As Worksheet

Set s = Sheets.Add

s.Shapes.AddLine(10, 10, 250, 250).Select

End Sub
'

When I open the workbook there is only a single worksheet. Routine "sam"
always runs fine, it always adds a new worksheet and draws the line. Routine
"bob" should do the same thing but it will give me the "object defined
error" when it is run the first time after file open; it does add the new
worksheet but then error results. After the error is dismissed "bob"
subsequently runs properly and without error...except that if all the newly
added sheets are manually deleted it again returns the error the first time
it is run.

The really weird thing is that "bob" will run fine if I step (F8) through it
under the same conditions that normally result in the error.

I don't understand why "bob" is inconsistent. I think I lifted the line
"s.Shapes.AddLine(10, 10, 250, 250).Select" from Excel VBA Help since it
seemed more efficient.

Anyone know why this line fails?

thanks!

--

Dave Peterson