how to create button?

[Guest]

Hi,
How to create new button in excel (not in userform) by using vba program.
HOw to assign new macro to the new create button in excell file.

Any ideas, suggestion?

Thanks in advance.

Rgrds,
Gin Lye

 

[Guest]

hi,
on the excel toolbar, click view,toolbars,control toolbox.
the control toolbox toolbar has several controls that can be dropped onto
the sheet. there is a button control. you can size the button to any size you
want. right click the button, click view code. here you can assign macros.

good luck

FSt1

 

[L.White]

I don't know how to place the button on the sheet without the VBA toolbar. I
always click on the button option on that toolbar and then just draw it in.
However, after the button is on the sheet right click on the sheet and click
view code. You will see a line that says

Private Sub CommandButton1_Click()

End Sub

Place the code for the macro inside this. If you have recorded the macro you
will be able to go to the module, copy the macro out and place it in the
button. Otherwise, happy coding.

LWhite

 

[Norman Jones]

Hi Gin Lye,

Try:

Sub Macro1()
Dim myButton As Button
Set myButton = ActiveSheet.Buttons. _
Add(Left:=10, Top:=10, Height:=50, Width:=50)
myButton.OnAction = "MyMacro"
End Sub


Sub MyMacro()
MsgBox "Hello"
End Sub

Change the Top, Left, Height and Width values to siut.

 

[Norman Jones]

Hi Gin Lye,

Try:

Sub Macro1()
Dim myButton As Button
Set myButton = ActiveSheet.Buttons. _
Add(Left:=10, Top:=10, Height:=50, Width:=50)
myButton.OnAction = "MyMacro"
End Sub


Sub MyMacro()
MsgBox "Hello"
End Sub

Change the Top, Left, Height and Width values to siut.

 

[Bob Phillips]

Here is a full example


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

 

[Mike Fogleman]

Sub MakeButton()

ActiveSheet.Buttons.Add(240.75, 51, 99, 29.25).Select
With Selection
.OnAction = "Macro1"
.Characters.Text = "Click Me!"
End With
Range("A1").Select

End Sub

Play with the 4 numbers to position it on the sheet.

Mike F

 

[Guest]

Thank u all for responding to my problem.

Sub MakeButton()

ActiveSheet.Buttons.Add(240.75, 51, 99, 29.25).Select
With Selection
.OnAction = "Macro1"
.Characters.Text = "Click Me!"
End With
Range("A1").Select

End Sub

Play with the 4 numbers to position it on the sheet.

Mike F