Writing code to create code

[T]

I want to generate some code which will add a button and its code to a worksheet Sheet1.

Adding the button is fine but I get a 'Programmatic access to Workbook not trusted' and a Debug error of
'VBProject of Object Workbook failed' error.

At the moment I'm just trying to add the basic Sub() and End Sub() part of the code.

Can anyone see what's wrong with this?

Sub WriteCode()

Dim NewButton As Object

Set NewButton = Sheets(1).OLEObjects.Add _("Forms.CommandButton.1")

With NewButton
.Left = 10
.Top = 10
.Width = 100
.Height = 25
.Object.Caption = "Add Lesson _ Code"
End With

Code = "Sub CommandButton1_Click()"

Code = Code & vbCrLf

Code = Code & "End Sub" & vbCrLf

With ThisWorkbook.VBProject

.VBComponents _("Sheet1").CodeModule
NextLine = .CountOfLines + 1
.InsertLines NextLine, Code

End With

End Sub

Cheers in advance.
This message was edited by T on 2002-09-17 21:10

 

[T]

This works fine, but I have just discovered that it produces an error with Office XP:
Run-time error '1004':
Programmatic access to Visual Basic Project is not trusted.

Just found the answer elsewhere on the board.

Go to TOOLS|MACROS|SECURITY|TRUSTED SOURCES and check TRUST ACCESS TO VISUAL BASIC PROJECT (Thanks Al Chara)

If various users are going to be running the program on various computers does anyone know how I can automate the above?

I tried recording the sequence of actions with the Macro Record editor but it came up with no code.

Thanks in advance.
This message was edited by T on 2002-09-17 21:24

 

[T]

But still getting an "Invalid Use of Property' error.

Any ideas?
This message was edited by T on 2002-09-17 21:27