Add an Excel VBA command button programmatically

Adding a VBA Command Button with its respective code is not a complicated task. It just requires a little knowledge of Excel and the macros. This article will show you how.

What is the VBA command button code?

With the VBA, buttons can invoke the macros, display the messages and perform other functions. Use VBA in order to add more functionality to Excel worksheets without possessing knowledge of technical details of the VBA Command Button properties or the functions such as the VBA Command Button click.

Copy the code to the respective module and confirm that the button is in action. One can increase the functionality of the worksheets by adding a VBA Command Button with its respective code. Office Software is clearly more developed than ever before.

Paste these two sections of code into a general module (Module1 for example).

Sub CreateButton()            
Dim Obj As Object            
Dim Code As String            
Sheets("Sheet1").Select            
'create button            
    Set Obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _            
    Link:=False, DisplayAsIcon:=False, Left:=200, Top:=100, Width:=100, Height:=35)            
    Obj.Name = "TestButton"            
'button text            
    ActiveSheet.OLEObjects(1).Object.Caption = "Test Button"            
'macro text            
    Code = "Sub ButtonTest_Click()" & vbCrLf            
    Code = Code & "Call Tester" & vbCrLf            
    Code = Code & "End Sub"            
'add macro at the end of the sheet module            
    With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule            
        .insertlines .CountOfLines + 1, Code            
    End With            
End Sub            
Sub Tester()            
    MsgBox "You have click on the test button"            
End Sub