Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using A Macro To Add Event Procedure To A CommandButton
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using A Macro To Add Event Procedure To A CommandButton
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) wrote in message oups.com... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to write a macro to import an event procedure to ThisWorbook? | Excel Programming | |||
Using A Macro To Add Event Procedure To A CommandButton | Excel Programming | |||
Event Procedure | Excel Programming | |||
Event Procedure again | Excel Programming | |||
OnTime event not firing in Workbook_Open event procedure | Excel Programming |