Add a button to an Excel Spreadsheet

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

AW

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.

Thanks

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
        Err.Clear
        Set objExcel = CreateObject("Excel.Application")
        If Err.Number <> 0 Then
            MsgBox "Cannot create excel object. " & Err.Description
            Exit Sub
        End If
    End If
   
    objExcel.Application.workbooks.Add
    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 _
            :=35.25).Select
    End With
   
    objWorkbook.saveas "C:\mystuff\testxb.xls"
    objWorkbook.Close SaveChanges:=False
   
    objExcel.Application.Quit
   
    Set objWorkbook = Nothing: Set objExcel = Nothing
   
End Sub

harfang - thats exactly what I need, many thanks!

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