I am using an Excel/VBA application to read SQL databases - then
create a pivot table using that data and put it on a new workbook.

Now I am trying to programmatically add a command button to a sheet on
that new workbook and put code behind it (meaning adding a SUB
procedure to the workbook as well, that is executed by a Click event
on the new command button).

I have the button added and renamed - now I am trying to figure out
how to add a Sub Procedure and then the Click event on that command
button.

Any suggestions??

Here is an example

Dim StartLine As Long

With ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule
StartLine = .CreateEventProc("Click", "CommandButton1") + 1
.InsertLines StartLine, _
"Dim ans" & vbCrLf & _
" ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
" If ans = vbNo Then Cancel = True"
End With



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



<(E-Mail Removed)> wrote in message
news:1650ad93-f01c-44b5-a6be-(E-Mail Removed)...
>I am using an Excel/VBA application to read SQL databases - then
> create a pivot table using that data and put it on a new workbook.
>
> Now I am trying to programmatically add a command button to a sheet on
> that new workbook and put code behind it (meaning adding a SUB
> procedure to the workbook as well, that is executed by a Click event
> on the new command button).
>
> I have the button added and renamed - now I am trying to figure out
> how to add a Sub Procedure and then the Click event on that command
> button.
>
> Any suggestions??

On Feb 5, 3:21*pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> Here is an example
>
> Dim StartLine As Long
>
> * * With ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule
> * * * * StartLine = .CreateEventProc("Click", "CommandButton1") + 1
> * * * * .InsertLines StartLine, _
> * * * * "Dim ans" & vbCrLf & _
> * * * * " * ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
> * * * * " * If ans = vbNo Then Cancel = True"
> * * End With
>
> --
> ---
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> <kjami...@travelers.com> wrote in message
>
> news:1650ad93-f01c-44b5-a6be-(E-Mail Removed)...
>
>
>
> >I am using an Excel/VBA application to read SQL databases - then
> > create a pivot table using that data and put it on a new workbook.
>
> > Now I am trying to programmatically add a command button to a sheet on
> > that new workbook and put code behind it (meaning adding a SUB
> >procedureto the workbook as well, that is executed by a Click event
> > on the new command button).
>
> > I have the button added and renamed - now I am trying to figure out
> > how to add a SubProcedureand then the Click event on that command
> > button.
>
> > Any suggestions??- Hide quoted text -
>
> - Show quoted text -

THANKS - worked like a charm.

On Feb 5, 3:21*pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> Here is an example
>
> Dim StartLine As Long
>
> * * With ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule
> * * * * StartLine = .CreateEventProc("Click", "CommandButton1") + 1
> * * * * .InsertLines StartLine, _
> * * * * "Dim ans" & vbCrLf & _
> * * * * " * ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
> * * * * " * If ans = vbNo Then Cancel = True"
> * * End With
>
> --
> ---
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> <kjami...@travelers.com> wrote in message
>
> news:1650ad93-f01c-44b5-a6be-(E-Mail Removed)...
>
>
>
> >I am using an Excel/VBA application to read SQL databases - then
> > create a pivot table using that data and put it on a new workbook.
>
> > Now I am trying to programmatically add a command button to a sheet on
> > that new workbook and put code behind it (meaning adding a SUB
> >procedureto the workbook as well, that is executed by a Click event
> > on the new command button).
>
> > I have the button added and renamed - now I am trying to figure out
> > how to add a SubProcedureand then the Click event on that command
> > button.
>
> > Any suggestions??- Hide quoted text -
>
> - Show quoted text -

OK - works like a charm - but opens up the VBE Project window - how do
you close that programatically??