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").Co deModule
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)



wrote in message
...
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" wrote:
Here is an example

Dim StartLine As Long

* * With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
* * * * 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)

wrote in message

...



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" wrote:
Here is an example

Dim StartLine As Long

* * With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
* * * * 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)

wrote in message

...



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