Adding a VBA Command Button

Latest update on June 10, 2020 at 04:02 AM by David Webb .

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

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"

'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

Download:

Server 1: https://sd-5b.archive-hosom/membres/up/b05c9f3873215e8d2df70f056b74dce61198c32d/ASTUCES/Ajouter_boutons_et_codes.xls

Photo by Mimi Thian on Unsplash