Playing arround with this routine, It does everything expected by creating
the button, but when it finnishes it stays in the code module. Is there
anyway after the routine finnishes that it can revert back to the worksheet.
The code is as follows:

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

Thank you in advance for you assistance.
--
Larry E. Brueshaber

You would have to start it with a keyboard command or worksheet/workbook event.

"Larry" wrote:

> Playing arround with this routine, It does everything expected by creating
> the button, but when it finnishes it stays in the code module. Is there
> anyway after the routine finnishes that it can revert back to the worksheet.
> The code is as follows:
>
> '-----------------------------------------------------------------
> 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
>
> Thank you in advance for you assistance.
> --
> Larry E. Brueshaber

Thank you for your timely response
--
Larry E. Brueshaber


"Larry" wrote:

> Playing arround with this routine, It does everything expected by creating
> the button, but when it finnishes it stays in the code module. Is there
> anyway after the routine finnishes that it can revert back to the worksheet.
> The code is as follows:
>
> '-----------------------------------------------------------------
> 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
>
> Thank you in advance for you assistance.
> --
> Larry E. Brueshaber