E
--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
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