Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.basic.visual.misc > #93

Re: VB6 to Excel

Show all headers | View raw

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

Back to comp.lang.basic.visual.misc | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

VB6 to Excel "Ivar" <ivar.ekstromer000@ntlworld.com> - 2011-04-24 13:01 +0100
  Re: VB6 to Excel GS <gs@somewhere.net> - 2011-04-24 09:40 -0400
    Re: VB6 to Excel "Ivar" <ivar.ekstromer000@ntlworld.com> - 2011-04-24 16:04 +0100
      Re: VB6 to Excel ralph <nt_consulting64@yahoo.net> - 2011-04-24 10:54 -0500
      Re: VB6 to Excel GS <gs@somewhere.net> - 2011-04-24 12:28 -0400
      Re: VB6 to Excel GS <gs@somewhere.net> - 2011-04-24 14:58 -0400
    Re: VB6 to Excel Steve Rindsberg <steve@rdpslides.com> - 2011-04-24 12:51 -0400
      Re: VB6 to Excel GS <gs@somewhere.net> - 2011-04-24 13:44 -0400
  Re: VB6 to Excel ralph <nt_consulting64@yahoo.net> - 2011-04-24 12:23 -0500
    Re: VB6 to Excel ralph <nt_consulting64@yahoo.net> - 2011-04-24 12:30 -0500
    Re: VB6 to Excel Steve Rindsberg <steve@rdpslides.com> - 2011-04-25 15:57 -0400
      Re: VB6 to Excel GS <gs@somewhere.net> - 2011-04-25 16:38 -0400
        Re: VB6 to Excel Steve Rindsberg <steve@rdpslides.com> - 2011-04-26 19:54 -0400
          Re: VB6 to Excel GS <gs@somewhere.net> - 2011-04-26 23:06 -0400

csiph-web