Using A Macro To Add Event Procedure To A CommandButton

[donna.gough]

Hi Folkes,
I started a thread a few weeks ago trying to do the above. I am back
on the problem and have tried to use the responses I recieved and
relate them back to Chip Pearson's 'Creating An Event Procedure' but am
getting a Memory Error.

I have all macros in a personal.xls that automatically opens. I have
one file open with a button on sheet1 (CommandButton1 with caption
"Button 1"). I use the following code to copy the button to Sheet2:-

Windows("Button_Code_1.xls").Activate
ActiveSheet.Shapes("CommandButton1").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A3").Select
ActiveSheet.Paste

I then want to add the code to the new button on sheet2 using the
following code:-

Dim startline As Long
With
ActiveWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule
startline = .CreateEventProc("Click", "CommandButton1")
.InsertLines startline, _
"Range(""A8"")=""Hello World"""
End With

I can step through the code until the .InsertLine and then I get the
following error:-

Excel.Exe - Application Error
The instrucion at....referenced memory....
The memory could not be read


I have the following references available
Visual Bacic for Applications
Microsoft Excel 9.0 for Applications
OLE Automation
Microsoft Office 9.0 Object Library
Microsoft Visual Basic for Applications Extensibility 5.3
I also have the macro security "trusted sources" activated.

Can anybody tell me if there is anything wrong with the above code?
Thanks for any responses.

 

[Bob Phillips]

I tried the code, and it works fine for me. A couple of observations though.

First, the code you have would put the actual statements outside of the
event, because you don't increment the startline. So instead of
startline = .CreateEventProc("Click", "CommandButton1")
use
startline = .CreateEventProc("Click", "CommandButton1") + 1

Secondly, this assumes a commandbutton from the control toolbox, not the
forms toolbar. Is yours this type?

--

HTH

RP
(remove nothere from the email address if mailing direct)