OLEObjects.ADD causes excel vba project to stop / reset / go dead

[Timothy Marks]

This is pretty simple. I want to add a button to my
sheet--programatically. When I do it programatically the project stops.
Is there a workaround?

ThisWorkbook.Worksheets("Market
Report").OLEObjects.ADD(ClassType:="Forms.CommandButton.1", Link:=False
_
, DisplayAsIcon:=False, Left:=2.25, Top:=256.5, Width:=18.75,
Height _
:=9).Select

Timothy Marks

 

[Dave Peterson]

Are you stepping through the code when it stops (using F8's?). If yes, then
don't step through it.

Maybe set a break point after that line of code???

 

[Timothy Marks]

Nope. It all resets and dissapears, cleared all breaks. When the item
finishes it appears the command button that I just created has the
focus and it appears to be in development mode on the sheet with all
the little control dots around it i.e.

.. . .
.. . .

 

[Dave Peterson]

Sorry, I don't have a guess.

Nope. It all resets and dissapears, cleared all breaks. When the item
finishes it appears the command button that I just created has the
focus and it appears to be in development mode on the sheet with all
the little control dots around it i.e.

. . .
. . .

 

[Chris Lavender]

Hi Tim
If you're using XL97or below (they fixed it after that) then you need to
tell XL to remove the focus from the command button. I used to find
something simple like Range("A1").Select did the trick OK.

HTH
Best rgds
Chris Lav

 

[Jim Thomlinson]

Here is what I use...

Dim wks As Worksheet
Dim wbk As Workbook
Dim cmdButton As OLEObject

Set wks = ActiveSheet
Set wbk = ActiveWorkbook

Set cmdButton = wks.OLEObjects.Add(ClassType:="Forms.CommandButton.1",
Height:=23, Width:=80)

With cmdButton
.Top = ActiveCell.Top
.Left = ActiveCell.Left
.Object.Caption = "Tada"
.Object.FontSize = 10
.Object.Font.Bold = False
.Object.Font.Underline = False
.Name = "cmdMyButton"
.PrintObject = False
End With

 

[Timothy Marks]

I have Excel 2003 SP1. I restarted Excel, I set a reference to a range
way off the sheet. The code goes to set the reference but then the
project still ends. Something I have the code snippet I have which I
copied from a macro is allowing it to go into debug mode. Any ideas?

 

[Timothy Marks]

Jim I did try that but that does not seem to work either. Do i have
some enviormental setting that may be causing this.

Timothy.

 

[Jim Thomlinson]

Not sure if this causes a problem in 2003, but you could try...
Select Tools -> Macro's -> Security -> Trusted Sources. Check Allow Access
to Visual Basic Project. I don't use 2003 here at work so I can ot test it
for you...

 

[Dave Peterson]

Maybe there is.

Close excel and clean up that windows temp file.

Windows start button|Run
%temp%

Excel stores lots of stuff associated with objects in this temp folder. Maybe
there are lots of them and excel is getting confused.

ps. You may want to close other applications when you do this so you don't
break anything. (I don't bother, but some do.)

 

[Timothy Marks]

I have both checked, Trust access to VBP and Trust all removed all add
ins and templates. I then deleted all trusted sources and add-ins.
Nothing has worked.

 

[Timothy Marks]

I rebooted my computer, I then cleared out my temp directory. All apps
were restarted. Still have the same result.

 

[Dave Peterson]

Your code worked fine for me in my simple test workbook.

Does it work in a brand new workbook for you?

(I got nothing either way, though.)