VBA create new workbook and create command button with code in it

Question

Good morning

What I'm trying to do is using a command button in an Excel file in order to execute the following operations automatically:

Create a new workbook (new excel file)
Generate a command button in it
Have the command button with code in it (already programmed without associating an existing macro with it, it should already contain its own code)

All these operations should be thone following the instructions written for the first command button.

No problem about creating the new document, but generating a command button in it already programmed is hard for me.

I'm a beginner with vba

Thanks a lot

I have done nothing regarding point 2 and 3 because I don't know how to do that — Alessandro Silecchia, Nov 10 '17 at 11:38
Possible duplicate of [How to add a command button in VBA?](https://stackoverflow.com/questions/31684157/how-to-add-a-command-button-in-vba) — GothicAnatomist, Nov 10 '17 at 12:51

score 1 · Answer 1 · answered Nov 10 '17 at 13:09

I found this to get you started. It creates a new sheet with a command button on it. You'll have to make some changes, but it's a good starting point for you. It's from Tim Williams on this site. Tim's a very seasoned coder !!

Sub wdlsinflow()

Dim sht As Worksheet
Dim Obj As Object
Dim Code As String
Dim cmod

Set sht = Sheets.Add(After:=Sheets(Sheets.Count))

With sht
    .Name = "blah"
    .Cells.Clear


    Set Obj = .OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
                           Link:=False, DisplayAsIcon:=False, Left:=200, _
                           Top:=100, Width:=100, Height:=35)

    Obj.Name = "ButtonTest" '<< name must match code below...
    Obj.Object.Caption = "Test Button"

    Code = "Sub ButtonTest_Click()" & vbCrLf & _
            "   Call Tester" & vbCrLf & _
            "End Sub"

    With .Parent.VBProject.VBComponents(.CodeName).CodeModule
        .insertlines .CountOfLines + 1, Code
    End With
End With

End Sub

Thank you Mitch. But now, where should I write the action to be made by the "ButtonTest"? Should I create a new Sub, should I write it in the Code strings... No idea about it... Thank you — Alessandro Silecchia, Nov 13 '17 at 10:57
Add a module, then add a sub like this `Sub Tester()` then follow with whatever code you want the button to do. Of course, you can change any of the names to fit your situation. — Mitch, Nov 13 '17 at 16:01

VBA create new workbook and create command button with code in it

1 Answers1