Create Control Button
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.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
Thank you in advance for you assistance.
--
Larry E. Brueshaber
|