Adding a VBA Command Button

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.

How to Add a VBA Command Button

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


Photo by Mimi Thian on Unsplash

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « Adding a VBA Command Button », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (