Creating a procedure using VBA Code

[kjamison]

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

 

[Bob Phillips]

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)

 

[kjamison]

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)









- Show quoted text -

THANKS - worked like a charm.

 

[kjamison]

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)









- Show quoted text -

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