Programatically adding code to a worksheet
 

I've written a macro to add some controls (i.e. buttons) to a
worksheet (not form).

2 questions

1. How do I add the code for the controls, like 'Private Sub
CommandButton1_Click()', to the worksheet that contains the control
programatically.

2. does this code have to be attached to the sheet that contains the
controls?

Thanks

Programatically adding code to a worksheet
 

1.

Dim StartLine As Long

With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
StartLine = .CreateEventProc("Click", "CommandButton1") + 1
.InsertLines StartLine, _
"Dim ans" & vbCrLf & _
" ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
" If ans = vbNo Then Cancel = True"
End With

2. Yes

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Flash in the Pan" wrote in message
...
I've written a macro to add some controls (i.e. buttons) to a
worksheet (not form).

2 questions

1. How do I add the code for the controls, like 'Private Sub
CommandButton1_Click()', to the worksheet that contains the control
programatically.

2. does this code have to be attached to the sheet that contains the
controls?

Thanks

Programatically adding code to a worksheet
 

Bob...

Tried what you wrote and changed "Sheet1" to a valid sheet in my
workbook.

When I ran it, received the following error.

Run-time error 57017
Event handler is invalid

Debug showed line containing Startline highlighted.


Any ideas?

Thanks



On May 19, 1:40*am, "Bob Phillips" wrote:
1.

Dim StartLine As Long

* * With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
* * * * StartLine = .CreateEventProc("Click", "CommandButton1") + 1
* * * * .InsertLines StartLine, _
* * * * "Dim ans" & vbCrLf & _
* * * * " * ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
* * * * " * If ans = vbNo Then Cancel = True"
* * End With

2. Yes

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Flashin thePan" wrote in ...



I've written a macro to add some controls (i.e. buttons) to a
worksheet (not form).

2 questions

1. How do I add the code for the controls, like 'Private Sub
CommandButton1_Click()', to the worksheet that contains the control
programatically.

2. does this code have to be attached to the sheet that contains the
controls?

Thanks- Hide quoted text -

- Show quoted text -

Programatically adding code to a worksheet
 

Hello Flash,

I had a similar issue. I needed to add code behind the sheet, but
using a macro.. Sort of using a macro to add another macro to the
excel workbook.
I used the following syntax...

With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
.InsertLines .CountOfLines + 1, _
"Private Sub Worksheet_Calculate()" & vbCrLf & _

Continue to add in all your lines of code, but enter them between " "
and terminate the line using & vbCrLf & _
Please note that VBA will note take in more than 25 lines of code. If
you happened to exceed the specified limit, VBA will pop an error !!
If you need to add in more code, start again using the .InsertLines
syntax.

Hope this helps !!!
Sagar






On Jun 2, 4:58*pm, Flash in the Pan wrote:
Bob...

Tried what you wrote and changed "Sheet1" to a valid sheet in my
workbook.

When I ran it, received the following error.

Run-time error 57017
Event handler is invalid

Debug showed line containing Startline highlighted.

Any ideas?

Thanks

On May 19, 1:40*am, "Bob Phillips" wrote:



1.

Dim StartLine As Long

* * With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
* * * * StartLine = .CreateEventProc("Click", "CommandButton1") + 1
* * * * .InsertLines StartLine, _
* * * * "Dim ans" & vbCrLf & _
* * * * " * ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
* * * * " * If ans = vbNo Then Cancel = True"
* * End With

2. Yes

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Flashin thePan" wrote in ...

I've written a macro to add some controls (i.e. buttons) to a
worksheet (not form).

2 questions

1. How do I add the code for the controls, like 'Private Sub
CommandButton1_Click()', to the worksheet that contains the control
programatically.

2. does this code have to be attached to the sheet that contains the
controls?

Thanks- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -