Adding a VBA CommandButton with its respective the code

November 2016

Adding a VBA CommandButton with its respective code is not a complicated task. It just requires a little knowledge of Excel and the macros. With the VBA, buttons can invoke the macros, display the messages and perform other functions. Use VBA inorder to add more functionality to Excel worksheets without possessing knowledge of technical details of the VBA CommandButton properties or the functions such as the VBA CommandButton 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 CommandButton 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            


'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"            
'buttonn 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


Related :

This document entitled « Adding a VBA CommandButton with its respective the code » from CCM ( is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.