A few words of thanks would be greatly appreciated.

Adding a VBA CommandButton with its respective the code

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


A few words of thanks would be greatly appreciated.

Ask a question
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team

Original article published by . Translated by netty5. Latest update on by lermite222.

This document, titled "Adding a VBA CommandButton with its respective the code," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (https://ccm.net/).