Programatically adding code to a worksheet

[Flash in the Pan]

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

 

[Bob Phillips]

1.

Dim StartLine As Long

With ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule
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]

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

 

[Sagu]

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").CodeModule
.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