VBA Buttons


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: VBA Buttons

  1. #1
    Dave Mac Guest

    VBA Buttons


    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


  2. #2
    Sam Barrett Guest

    Re: VBA Buttons


    "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


  3. #3
    Arthur Wood Guest

    Re: VBA Buttons


    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
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center