Add a Button with code

[Alan Smykowski]

Is there any way I can add a command button using code
when a new worksheet is added to a workbook, the button is
called formatsheet and it calls formatsheet(), its
position will be left 1.5, top 14.25 Height 24.5 its
caption is ReFormat Sheet and it is not to be printed

Thanks in Advance

 

[Bob Phillips]

Here is a general code snippet that shows you how to do it, it adds a button
and some click event code for it.

'-----------------------------------------------------------------
Sub CreateControlButton()
'-----------------------------------------------------------------
Dim oWs As Worksheet
Dim oOLE As OLEObject

Set oWs = ActiveSheet

Set oOLE =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Left:=200, Top:=100, Width:=80, Height:=32)

'To set with a cell
'With Range("H2")
' Set oOLE =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
' Left:=.Left, Top:=.Top, Width:=.Width,
Height:=.Height)
'End With

With oOLE
.Object.Caption = "Run myMacro"
.Name = "myMacro"
End With

With ThisWorkbook.VBProject.VBComponents(oWs.CodeName).CodeModule
.InsertLines .CreateEventProc("Click", oOLE.Name) + 1, _
vbTab & "If Range(""A1"").Value > 0 Then " & vbCrLf & _
vbTab & vbTab & "Msgbox ""Hi""" & vbCrLf & _
vbTab & "End If"

End With

End Sub
'-----------------------------------------------------------------
Sub CreateCombobox()
'-----------------------------------------------------------------
Dim oWs As Worksheet
Dim oOLE As OLEObject

Set oWs = ActiveSheet

Set oOLE = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Combobox.1", _
Left:=200, Top:=100, Width:=80, Height:=32)


oOLE.ListFillRange = "A1:A10"

End Sub

'-----------------------------------------------------------------


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Alan Smykowski]

Ok it will add the button but then I get the msgbox as
follows

Runtime error '1004'

Programmatic access to Visual Basic Project is not Trusted

-----Original Message-----
Here is a general code snippet that shows you how to do it, it adds a button
and some click event code for it.

'--------------------------------------------------------- --------
Sub CreateControlButton()
'--------------------------------------------------------- --------
Dim oWs As Worksheet
Dim oOLE As OLEObject

Set oWs = ActiveSheet

Set oOLE =
ActiveSheet.OLEObjects.Add

(ClassType:="Forms.CommandButton.1", _

Left:=200, Top:=100, Width:=80, Height:=32)

'To set with a cell
'With Range("H2")
' Set oOLE =
ActiveSheet.OLEObjects.Add

(ClassType:="Forms.CommandButton.1", _

' Left:=.Left, Top:=.Top, Width:=.Width,
Height:=.Height)
'End With

With oOLE
.Object.Caption = "Run myMacro"
.Name = "myMacro"
End With

With ThisWorkbook.VBProject.VBComponents (oWs.CodeName).CodeModule
.InsertLines .CreateEventProc("Click", oOLE.Name) + 1, _
vbTab & "If Range(""A1"").Value > 0 Then " & vbCrLf & _
vbTab & vbTab & "Msgbox ""Hi""" & vbCrLf & _
vbTab & "End If"

End With

End Sub
'--------------------------------------------------------- --------
Sub CreateCombobox()
'--------------------------------------------------------- --------
Dim oWs As Worksheet
Dim oOLE As OLEObject

Set oWs = ActiveSheet

Set oOLE = ActiveSheet.OLEObjects.Add

(ClassType:="Forms.Combobox.1", _

 

[Tom Ogilvy]

tools=>Macro=>Security, go to the trusted publishes and in the lower left
corner click
Trust Acces to Visual Basic Project

 

[Dave Peterson]

And just to add to Tom's response...

This is a setting that doesn't travel with the workbook--it's a user setting.
If other users are going to run this code, they'll have to change this setting,
too.

 

[jvanepps]

Getting Compile Error Expected: End of Statement

I tried modifying the code to meet my needs, and have given a snipet below...

With ThisWorkbook.VBProject.VBComponents(oWs.CodeName).CodeModule
.InsertLines .CreateEventProc("Click", oOLE.Name) + 1, _
vbTab & "ActiveSheet.Copy After:=Sheets(1)" & vbCrLf & _
vbTab & "ActiveSheet.PageSetup.LeftHeaderPicture.Filename = _ " & vbCrLf & _
vbTab & vbTab & ""http://kaplank12.achieva.com/KAP/images/global/logo.gif"" & vbCrLf & _

I keep getting the Compile Error Expected: End of Statement.

Any ideas what is wrong?