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
– Alessandro Silecchia, Commented Nov 10, 2017 at 11:38

Mitch · Accepted Answer · 2017-11-10 13:09:20Z

1

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

answered Nov 10, 2017 at 13:09

Mitch

5833 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alessandro Silecchia Over a year ago

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

Mitch Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related