Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!news.dougwise.org!nntpfeed.proxad.net!proxad.net!feeder1-1.proxad.net!198.186.194.247.MISMATCH!transit3.readnews.com!textspool1.readnews.com!news-out.readnews.com!postnews7.readnews.com!not-for-mail
Date: Sun, 24 Apr 2011 12:51:50 -0400
Newsgroups: comp.lang.basic.visual.misc
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
X-Newsreader: Virtual Access Open Source http://www.virtual-access.org/
Organization: RDP
Message-Id: <VA.000001cd.b50ab3dc@rdpslides.com>
Subject: Re: VB6 to Excel
From: Steve Rindsberg <steve@rdpslides.com>
Reply-To: steve@rdpslides.com
References: <aiUsp.26$2z7.14@newsfe15.ams2> <ip194v$a7k$1@dont-email.me>
Lines: 43
NNTP-Posting-Host: 7d834d21.news.iglou.com
X-Trace: DXC=Io\hVVVlYA]6KTSWD=W;1_?d1JL[FQ2iUlBfU`ioE8K[KZ:c?9jk:o]QRem6<;fd0WRhh1TUjBib^
X-Complaints-To: abuse@iglou.com
Xref: x330-a1.tempe.blueboxinc.net comp.lang.basic.visual.misc:97


>   With xlSheet
>     .Shapes.AddFormControl xlButtonControl, 100, 100, 100, 100
>     With .Shapes(1)
>       'Control must be selected to edit text
>       .Select: Selection.Characters.Text = "ButtonCaption"
>       .OnAction = "MacroName"
>     End With
>     .Cells(1).Select 'exit edit text mode
>   End With

I'd do this instead:

' Set up a variable to hold a reference to the shape
' you're going to create:
Dim oSh as Shape

With xlSheet
    ' create the shape and get a ref to it
    Set oSh = .Shapes.AddFormControl( xlButtonControl, 100, 100, 100, 100)
    ' now you can work with the shape
    ' and don't have to rely on it being the first shape on the sheet
  With oSh
    'Control must be selected to edit text
    .Select: Selection.Characters.Text = "ButtonCaption"
    .OnAction = "MacroName"
  End With
  .Cells(1).Select 'exit edit text mode
End With

You can also avoid selecting it if you do this:

    With oSh.OLEFormat.Object
        .Caption = "Your Text Here"
    End With

And inside that With/End With context you can work with any of the other 
properties you'd normally see when you rightclick/Properties.  Intellisense 
won't help you but the Properties dialog will give you the names of the 
available properties.