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 wrote:
>
> 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
>
>
--
Dave Peterson
|
Similar Threads
