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

Timothy Marks

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

--

Dave Peterson

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.

.. . .
.. . .

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> . . .
> . . .
>

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

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

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.

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

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

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.

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

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