Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!news.albasani.net!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail
From: GS <gs@somewhere.net>
Newsgroups: comp.lang.basic.visual.misc
Subject: Re: VB6 to Excel
Date: Sun, 24 Apr 2011 09:40:51 -0400
Organization: A noiseless patient Spider
Lines: 53
Message-ID: <ip194v$a7k$1@dont-email.me>
References: <aiUsp.26$2z7.14@newsfe15.ams2>
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-15"; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Date: Sun, 24 Apr 2011 13:38:39 +0000 (UTC)
Injection-Info: mx01.eternal-september.org; posting-host="UVJBuiIZprDyxzUkJsiwfw"; logging-data="10484"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX18UQMeyrRmnxd2POgvwhBQF"
X-Antivirus-Status: Clean
X-Newsreader: MesNews/1.08.03.00-gb
X-Antivirus: avast! (VPS 110423-1, 04/23/2011), Outbound message
Cancel-Lock: sha1:FSyVfuJbv3qe6ZujmPfgYKz4gUc=
X-ICQ: 543516788
Xref: x330-a1.tempe.blueboxinc.net comp.lang.basic.visual.misc:93

Ivar pretended :
> Hi All.
>
> I'm using VB6 to open a .xls file and put various values, formulas and 
> formats in to the spreadsheet,
> All works well using reference to Excel 10 Object library.
>
> I can put a button on the sheet using something like this:
> xlsheet.Shapes.AddFormControl xlButtonControl, 100, 100, 100, 100
> However, 2 things I can't find any sample or help on is
> How to set the Caption Property of the CommandButton and
> How to put any VBA code in the click event of the button, even if it's just 
> MsgBox "Hello World"
> Or
> xlsheet.Shapes(1).OnAction = "Macro1"
> Cant find any info on how to add a Macro to the excel work book
>
> Any Pointers please
>
> Ivar

Try...

  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 don't know how to programmatically insert code behind the worksheet, 
so hopefully someone will step up to help you with that. (I usually 
build an Excel XLT that's all set up to receive data, with layout, 
menus/controls, and formatting already in place)

<FYI>
It's always a good idea to go through a process manually and run 
Excel's macro recorder so it generates code. This should give you what 
you need to automate from VB6. Note that the generated code will need 
to be cleaned up some to get rid of the inefficient coding the macro 
recorder creates.
</FYI>

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc