Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using A Macro To Add Event Procedure To A CommandButton
I hope this won't be a duplicate of a post i have not long posted but
it seems to have disappeared! I am trying to combine responses I had a few weeks ago with Chip Pearsons 'Creating An Event Procedure'..I am slowly getting somewhere. I am using the following:- CellCel="E1" With ActiveWorkbook.VBProject.VBComponents(ActiveSheet. Name).CodeModule X = CountOfLines .InsertLines X + 1, "Sub CommandButton1_Click" .InsertLines X + 2, "d=" & CellSel .InsertLines X + 3, "End Sub" End With Which gives me:- Sub CommandButton1_Click() d = E1 End Sub How do I get d="E1"? As a side question: Why if I change the InsertLine to "Private Sub CommandButton1_click" do I get a "can't read memory error" when I step through the program and try to run the InsertLine X+2 line? I also get the read memory error when I try and use Chip Pearsons CreateEventProc procedure at the insertlines point....Any ideas why? i can post the code i am using for the createEventProc if it will be useful. Thank you for any responces. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using A Macro To Add Event Procedure To A CommandButton
It is a duplicate, and this was the response I gave to that
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) "Donna" wrote in message om... I hope this won't be a duplicate of a post i have not long posted but it seems to have disappeared! I am trying to combine responses I had a few weeks ago with Chip Pearsons 'Creating An Event Procedure'..I am slowly getting somewhere. I am using the following:- CellCel="E1" With ActiveWorkbook.VBProject.VBComponents(ActiveSheet. Name).CodeModule X = CountOfLines .InsertLines X + 1, "Sub CommandButton1_Click" .InsertLines X + 2, "d=" & CellSel .InsertLines X + 3, "End Sub" End With Which gives me:- Sub CommandButton1_Click() d = E1 End Sub How do I get d="E1"? As a side question: Why if I change the InsertLine to "Private Sub CommandButton1_click" do I get a "can't read memory error" when I step through the program and try to run the InsertLine X+2 line? I also get the read memory error when I try and use Chip Pearsons CreateEventProc procedure at the insertlines point....Any ideas why? i can post the code i am using for the createEventProc if it will be useful. Thank you for any responces. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using A Macro To Add Event Procedure To A CommandButton
Bob, Thanks for your replys. I have something now that works
perfectly for me....I still don't know why if I add 'Private' or use the CreateEventProc why it gives me a memory error but the following works. If you create a button then add code manually then it is a Private Sub it creates - Will it cause me any errors having a non-private sub. I am going to have 4 or 5 sheets each with the same Sub names. This is what I have and thank you for your input - I seem to have had such trouble getting my head round this one! i = ActiveSheet.Shapes.Count Line1 = "Sub CommandButton" & i & "_Click" ButPaste = InputBox("What Is Column Letter of First Column of New Chart? eg:- AA") CellSel = Range(ButPaste + "1") With ActiveWorkbook.VBProject.VBComponents(ActiveSheet. CodeName).CodeModule X = .CountOfLines .InsertLines X + 1, Line1 .InsertLines X + 2, "d=" & Chr(34) & CellSel .InsertLines X + 3, "Call Common" .InsertLines X + 4, "End Sub" End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using A Macro To Add Event Procedure To A CommandButton
wrote in message oups.com... Bob, Thanks for your replys. I have something now that works perfectly for me....I still don't know why if I add 'Private' or use the CreateEventProc why it gives me a memory error but the following works. Glad we made progress. If you create a button then add code manually then it is a Private Sub it creates - Will it cause me any errors having a non-private sub. I am going to have 4 or 5 sheets each with the same Sub names. Don't worry about that. Sheets are separate modules, so having the same name is not a problem. Excel will know which sheet is active and will invoke the code for that sheet. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using A Macro To Add Event Procedure To A CommandButton
Fantastic..Thank you.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using A Macro To Add Event Procedure To A CommandButton | Excel Programming | |||
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 | |||
OnTime event not firing in Workbook_Open event procedure | Excel Programming |