how to create button?

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

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


"Daniel" wrote:

> 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

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

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.


---
Regards,
Norman



"Daniel" <Daniel@discussions.microsoft.com> wrote in message
news:D11EA615-578A-498E-BC2F-CC91DAF7583F@microsoft.com...
> 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

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.


---
Regards,
Norman




"Daniel" <Daniel@discussions.microsoft.com> wrote in message
news:D11EA615-578A-498E-BC2F-CC91DAF7583F@microsoft.com...
> 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

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



--
HTH

Bob Phillips

"L.White" <lwhite@appliedcomposites.com> wrote in message
news:eSx7C$FjFHA.1416@TK2MSFTNGP09.phx.gbl...
> 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
>
>

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
"Daniel" <Daniel@discussions.microsoft.com> wrote in message
news:D11EA615-578A-498E-BC2F-CC91DAF7583F@microsoft.com...
> 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

Thank u all for responding to my problem.

"Mike Fogleman" wrote:

> 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
> "Daniel" <Daniel@discussions.microsoft.com> wrote in message
> news:D11EA615-578A-498E-BC2F-CC91DAF7583F@microsoft.com...
> > 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
>
>
>