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).
Dim Obj As Object
Dim Code As String
Set Obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=200, Top:=100, Width:=100, Height:=35)
Obj.Name = "TestButton"
ActiveSheet.OLEObjects(1).Object.Caption = "Test Button"
Code = "Sub ButtonTest_Click()" & vbCrLf
Code = Code & "Call Tester" & vbCrLf
Code = Code & "End Sub"
'add macro at the end of the sheet module
.insertlines .CountOfLines + 1, Code
MsgBox "You have click on the test button"