Adding a VBA CommandButton with its respective the code

February 2017

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



Published by netty5. Latest update on December 28, 2012 at 07:35 AM 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 (