Creating Command Buttons from VBA - adding code

[tcb]

The code doTEST_XLS below creates a command button. Once the button
is created, in VBA, how do I create the sub or function I want to
run? For example, if I want to create this MsgBox, what must I add to
my doTEST_XLS code?

Private Sub CommandButton1_Click()
MsgBox ("Hello World!")
End Sub

This creates the comand button:

Sub doTEST_XLS()

Dim objXL As Object
Dim strWhat As String, boolXL As Boolean
Dim objActiveWkb As Object

If fIsAppRunning("Excel") Then
Set objXL = GetObject(, "Excel.Application")
boolXL = False
Else
Set objXL = CreateObject("Excel.Application")
boolXL = True
End If

objXL.Application.Workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkbook

With objActiveWkb.ActiveSheet.OLEObjects.Add("Forms.CommandButton.
1")
.Left = 35
.Top = 200
.Object.Caption = "Hello World"
.Width = 100
.Height = 50

End With

objActiveWkb.Close savechanges:=True

If boolXL Then objXL.Application.Quit

Set objActiveWkb = Nothing: Set objXL = Nothing

End Sub

 

[Bob Phillips]

Assuming that it is in a worksheet, use

Dim StartLine As Long

With ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule
StartLine = .CreateEventProc("Click", "CommandButton1") + 1
.InsertLines StartLine, _
" MsgBox ""Hello World!"""
End With



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[tcb]

Assuming that it is in a worksheet, use

Dim StartLine As Long

With ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule
StartLine = .CreateEventProc("Click", "CommandButton1") + 1
.InsertLines StartLine, _
" MsgBox ""Hello World!"""
End With

To that I get "Run time error '1004': Programmatic access to Visual
Basic project is not trusted." How can I get by that?

 

[Dave Peterson]

In xl2003:
Tools|Macro|security...|Trusted publishers tab

But this is a user setting. If you're sharing your workbook with others, you'll
want to tell them to make the same security change.

There is no way you can include changing this setting in code.