Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 652
  • Last Modified:

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?
1 Solution
Rey Obrero (Capricorn1)Commented:
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

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

sbiddleAuthor Commented:
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.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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
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!

sbiddleAuthor Commented:
harfang - thats exactly what I need, many thanks!
Glad to help! And have fun with this, it can get really weird...

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now