Avatar of sbiddle

asked on 

Add a button to an Excel Spreadsheet

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?
Microsoft Access

Avatar of undefined
Last Comment
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

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

Avatar of Arthur_Wood
Flag of United States of America image

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).

Avatar of sbiddle


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.

Avatar of rockiroads
Flag of United States of America image

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
Avatar of harfang
Flag of Switzerland image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of sbiddle


harfang - thats exactly what I need, many thanks!
Avatar of harfang
Flag of Switzerland image

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

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo