Add a button to an Excel Spreadsheet

Posted on 2006-05-09
Last Modified: 2008-01-09
I have written a reports engine that creates a nicely formatted report pack as an excel spreadsheet.  It creates the spreadsheet from scratch.  

What I now need to do is add a few buttons onto the summary sheet and put some code behind the buttons.  I have code I want to add to the buttons but I am having difficulty actually adding it.

Can anyone help?
Question by:sbiddle
    LVL 119

    Expert Comment

    by:Rey Obrero
    You have to make the control toolbox visible first

    View>Toolbars>Control Toolbox

    Select the command button icon, move the cursor {+} to the sheet, click hold and drag to desired size.

    While the drawn Command bar still selected click the ViewCode from the toolbox to open the VBA window
    place the codes to space provided for the command button

    Private Sub CommandButton1_Click()
    '  Place codes here
    End Sub

    LVL 44

    Expert Comment

    Open the Spreadsheet, then make sure that the Visual Basic TOOLBAR is showing (click on the View menu item, then choose Toolbars, and then choose Visual Basic).  Then, from the Visual Basic Toolbar, click on the Design Mode button (looks like a Trianle resting on a ruler).  This will put the Spreadsheet in Desing Mode, and you can then add a Button (or Buttons) to the spreadsheet surface.

    You can right click on the newly drawn Button, then select Properties, and change the Default name of the Button, and the Caption of the Button, to match your requirements.  Then, when you double-click on the Button (in DEsign mode), you will be taken top the Code page, where you can add the necessary VBA code to handle the Button_Click event (or what ever other events youy want to add code for).

    LVL 3

    Author Comment

    Thanks for the answers guys but I don't think I was clear in my question, apologies for that.

    What I am trying to do is automate the whole thing from Access.  ie Create the spreasheet, then add the buttons and add the code to the buttons all from Access.

    LVL 65

    Expert Comment

    Excel Automation is what u need

    this little snippet of code will create a excel file with some text in cell 1 and a button
    Only drawback is code. I dont know how you gonna manage that, considering that code has to be registered with the object.

    The alternative is perhaps if you created template excel files, with your buttons and code behind it. Then in your code in MSAccess, u can make copies of whatever template is appropriate

    here is the code

    Public Sub CreateXLS()

        Dim objExcel As Object
        Dim objWorkbook As Object
        On Error Resume Next
        Set objExcel = GetObject(, "Excel.Application")
        If objExcel Is Null Then
            Set objExcel = CreateObject("Excel.Application")
            If Err.Number <> 0 Then
                MsgBox "Cannot create excel object. " & Err.Description
                Exit Sub
            End If
        End If
        Set objWorkbook = objExcel.Application.ActiveWorkBook
        With objWorkbook
            .worksheets(1).Cells(1, 1) = "England to win the World Cup"
            .worksheets(1).OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
                , DisplayAsIcon:=False, Left:=53.25, Top:=57.75, Width:=106.5, Height _
        End With
        objWorkbook.saveas "C:\mystuff\testxb.xls"
        objWorkbook.Close SaveChanges:=False
        Set objWorkbook = Nothing: Set objExcel = Nothing
    End Sub
    LVL 58

    Accepted Solution

    Hello sbiddle

    Just a bit late, but with more "feature" ;)
    Creates a worksheet, with a macro and  a button launching it...

        ' XL is an open Excel Application...
        With XL.Workbooks.Add
            With .VBProject.VBComponents.Add(1)
                .CodeModule.AddFromString _
                      "Sub Hello()" & vbCrLf _
                    & "    MsgBox ""hello world!""" & vbCrLf _
                    & "End Sub"
            End With
            With .Worksheets(1).Buttons.Add(50, 50, 50, 30)
                .OnAction = "Hello"
                .Caption = "Hello!"
            End With
            .Saved = True
        End With

    Have Fun!

    LVL 3

    Author Comment

    harfang - thats exactly what I need, many thanks!
    LVL 58

    Expert Comment

    Glad to help! And have fun with this, it can get really weird...

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now