Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Control Button
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Control Button
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.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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create control button on worksheet: Forms v. ActiveX feature? | Excel Discussion (Misc queries) | |||
create a control button to insert rows | Excel Discussion (Misc queries) | |||
How do I create a button or control that will automatically go to | Excel Worksheet Functions | |||
create commandbar / control button using codes | Excel Programming | |||
Command Button vs Control Button | Excel Programming |