Excel VBA add command button programatically

Excel VBA add command button programatically

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.

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

Download:

Visit our forum for more Microsoft excel tips!
Subject Replies
How to calculate positions(1st,2nd,3rd...) in Excel 4
Unable to change date format in Excel 39
How to convert number into text in Excel 9
Convert number to spelling 28
Date formatting in dd/mm/yyyy 11
Removing " false" value in "IF" 10
Automatically transfer data from one sheet to another 22
Comparing two columns of names in Excel 8
Excel - To generate unique ID 4
converting number into words (Excel) 6

Excel