Using A Macro To Add Event Procedure To A CommandButton

[Donna]

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.

 

[Bob Phillips]

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.gough]

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

 

[Bob Phillips]

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.

 

[donna.gough]

Fantastic..Thank you.