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

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.CommandB utton.1", Link:=False
_
, DisplayAsIcon:=False, Left:=2.25, Top:=256.5, Width:=18.75,
Height _
:=9).Select

Timothy Marks

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

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

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.CommandB utton.1", Link:=False
_
, DisplayAsIcon:=False, Left:=2.25, Top:=256.5, Width:=18.75,
Height _
:=9).Select

Timothy Marks

--

Dave Peterson

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

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.

.. . .
.. . .

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

Sorry, I don't have a guess.

Timothy Marks wrote:

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

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

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

"Timothy Marks" wrote in message
oups.com...
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.

. . .
. . .

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

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
--
HTH...

Jim Thomlinson


"Timothy Marks" wrote:

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.CommandB utton.1", Link:=False
_
, DisplayAsIcon:=False, Left:=2.25, Top:=256.5, Width:=18.75,
Height _
:=9).Select

Timothy Marks

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

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?

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

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.

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

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...
--
HTH...

Jim Thomlinson


"Timothy Marks" wrote:

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.

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

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

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.

--

Dave Peterson

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

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

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

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

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

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.)

Timothy Marks wrote:

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

--

Dave Peterson