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

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.Comma ndButton.1", _
Left:=200, Top:=100, Width:=80, Height:=32)

'To set with a cell
'With Range("H2")
' Set oOLE =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.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.Combo box.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" wrote in message
...
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

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", _
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"
wrote in message
...
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


.

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

--
Regards,
Tom Ogilvy


"Alan Smykowski" wrote in message
...
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", _
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"
wrote in message
...
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


.

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.



Tom Ogilvy wrote:

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

--
Regards,
Tom Ogilvy

"Alan Smykowski" wrote in message
...
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", _
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"
wrote in message
...
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


.


--

Dave Peterson