VBA Buttons

[Dave Mac]

Applologies, I'm a bit new to VBA...

I've created a button on an Excel Spreadsheet with the following code, and
cannot find out what the object is called so that I can use an expression
and change the buttons properties and act on a user clicking it.

ActiveSheet.OLEObjects.Add ClassType:="Forms.CommandButton.1", Link:=False,
DisplayAsIcon:=False, Left:=480, Top:=80, Width:=120, Height:=40

Could anyone please tell/e-mail me where I can find the information?
Is there a module or form like module for CommanButton code?(CommandButton_click)?
I cant find it and I've been going round in circles with MSHelp!! ;(

Any advice would be graciously appriciated.
Many Thanks in advance,

Dave Mac
mac@creations.co.uk

[Sam Barrett]

"Dave Mac" <Mac@creations.co.uk> wrote:
><snip>I've created a button on an Excel Spreadsheet with the following code,
and
>cannot find out what the object is called so that I can use an expression
>and change the buttons properties and act on a user clicking it.
>
>ActiveSheet.OLEObjects.Add ClassType:="Forms.CommandButton.1", Link:=False,
>DisplayAsIcon:=False, Left:=480, Top:=80, Width:=120, Height:=40

Hi Dave! This had me stumped for a long time until Paul Clement pointed
out to me that the Add
methods and the collections return an object, so if you place the following
code in the sheet where you
want the button, it should work. After looking at this code, I understand
why I still use XL97 buttons.
To me they seem a lot simpler. Hope that this helps!

Sub MakeButton()
Dim myButton As OLEObject
Set myButton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1",
Link:=False, _
DisplayAsIcon:=False, Left:=480, Top:=80, Width:=120, Height:=40)
myButton.Object.Caption = "Press Here"
myButton.Name = "cmdTest"
End Sub

Private Sub cmdTest_Click()
MsgBox "Sucess!"
End Sub

Sam Barrett, MTL Systems, Inc., mailto:sam@mtl.com
Brainbench MVP, http://www.brainbench.com

And the things that you have heard...commit these to faithful
men who will be able to teach others also. 2 Timothy 2:2

[Arthur Wood]

you don't want to create the command button in code, since you can't then
add code to respond to the user clicking on it, as you have discovered.

In the Excel Spreadsheet, under the View menu, select Toolbars, and then
Control Toolbox. This will display the control toolbox, from which you can
then select a Command Button control, to draw on your spreadsheet. By default
this will be named Command1. Then, go to Tools, Macro, Visual Basic Editor,
and then select Sheet1. This will open the VBA IDE, and you can then select
then Command1 object from then Left hand drop-down list, just as you would
in the VB IDE. That will default to the Command1_Click event handler......

"Dave Mac" <Mac@creations.co.uk> wrote:
>
>Applologies, I'm a bit new to VBA...
>
>I've created a button on an Excel Spreadsheet with the following code, and
>cannot find out what the object is called so that I can use an expression
>and change the buttons properties and act on a user clicking it.
>
>ActiveSheet.OLEObjects.Add ClassType:="Forms.CommandButton.1", Link:=False,
>DisplayAsIcon:=False, Left:=480, Top:=80, Width:=120, Height:=40
>
>Could anyone please tell/e-mail me where I can find the information?
>Is there a module or form like module for CommanButton code?(CommandButton_click)?
>I cant find it and I've been going round in circles with MSHelp!! ;(
>
>Any advice would be graciously appriciated.
>Many Thanks in advance,
>
>Dave Mac
>mac@creations.co.uk
>