Code "crashes" after dynamically adding control to worksheet

[--elizabeth]

My code creates a worksheet and adds a forms commandbutton, both "on the fly".

Dim ws As Worksheet
Dim lStartLine As Long
Dim sCode as String
Set ws = Sheets("NewWorksheet")


My question is two parts:

PART I: For the coding below that doesn't work, I would like to know why.

If I use the following code to create the button, the code is added
successfully:
With ActiveWorkbook.VBProject.VBComponents(ws.CodeName).CodeModule
lStartLine = .CountOfLines + 1
.InsertLines lStartLine, sCode
End With

If I use this code, it crashes:
Set wsVBComp = ActiveWorkbook.VBProject.VBComponents(ws.CodeName)
wsVBComp.CodeModule.AddFromString sCode

If I use this code, I get a "Runtime error 57: device I/O error" (all on one
line):
ActiveWorkbook.VBProject.VBComponents
(ws.CodeName).CodeModule.AddFromFile sCode

If I use this code, a dialog window pops up saying "cant enter break mode at
this time" or IF it doesn't crash, adds code to module but then form doesn't
show
Excel.Application.VBE.ActiveVBProject.VBComponents (again all on one
line)(ws.CodeName).CodeModule.AddFromString sCode

PART II (a two-part question):

In the code above that DOES work:

A: Control does not return to calling procedure (UserForm_Initialize).

B: The code ("Private Sub cmdDelete_Click") for the created button does
appear in the code module for the created worksheet. But if I click on the
button on the worksheet, I get the following dialog box message:

"The macro 'WorkbookName.xls!Sheet7cmdDelete_Click' cannot be found."

There is nothing unusual about the code itself except for the above-noted.

Thanks,
--e

 

[--elizabeth]

Ooops! Somehow, I entered a key combination that posted my question before I
was ready. Before the lines below, should have been:

The declarations for the below code are as follows:

Dim ws As Worksheet
Dim lStartLine As Long
Dim sCode as String
Set ws = Sheets("NewWorksheet")

Thanks for helping.
--elizabeth

 

[Dave Peterson]

I don't really have a good guess why your code crashes.

But I've never been a fan of adding controls on the fly. If it's possible, can
you add the commandbutton that you want while you're in design mode. Then just
hide the commandbutton until you need it.

If you expect others to run the routine, it's a much safer route to go. There's
a security setting that each user sets (not you as the developer) that allows
programs to touch any workbook's project.

If they don't allow it, then your code won't work -- even if it didn't crash.

=====
If you're adding a new sheet, then I'd create a template sheet that had all the
code and controls that I needed.

And a guess about the #2 question...

If you're not calling that procedure, then it sounds like the control isn't
really a commandbutton from the Control Toolbox toolbar. It looks like it's a
button from the Forms toolbar.

And those are better behaved, too!

You could create the macro and put it in a general module and then add that
button (Forms type) to the worksheet and assign the macro to it. Even this
works better (feels safer to me) than adding the commandbutton and writing code
to write code.

============
I'm not sure what you're doing, but maybe you could dump the button and provide
a toolbar that allows the macro to run. (Or modify the QAT/Ribbon in
xl2007???).

Have I said how much I don't like the code that writes code stuff? Too much can
go wrong.

 

[--elizabeth]

Dave, Thanks for the suggestions. I'll implement them in my application.
--e