Adding buttons automatically VS Using Button-like-cells

To add ActiveX controls automatically on the sheet by VBA, write codes as follows:

Sub Add_OLEObject_Test()
   Dim objOLE As OLEObject
   Dim i      As Long
        Set objOLE = Worksheets("Sheet1").OLEObjects.Add("Forms.CommandButton.1")
        objOLE.Name = "CommandButton1"   'Name of the control
        objOLE.Left = 10           'Location of the control
        objOLE.Top = 10            'Location of the control
End Sub

We can add a new ActiveX button's code on the VBE by writing this way:

Sub WriteTest()
    n = Worksheets("Sheet1").OLEObjects.Count
    With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
        .InsertLines 1, vbTab & "Sub CommandButton" & n & "_Click"
        .InsertLines 2, vbTab & "msgbox ""under construction"""
        .InsertLines 3, vbTab & "End sub"
    End With
End Sub

n represents how many ActiveX controls are existing on the sheet.

n = Worksheets("Sheet1").OLEObjects.Count

That is to say, n counts how many ActiveX controls are exisiting; if there are 2 controls on the sheet, n becomes 2. Thus, by using the number and "InsertLines", we can automatically add codes in VBE for new controls we have added. But what if there is a small glitch and codes for the second button are inserted in line 2?  That would look like this:

Sub CommandButton1_Click()
Sub CommandButton2_Click()
End Sub
End Sub 

This never works properly and we can see how small glitch destroys the whole system. So I don't recommend writing codes this way to dynamically add new buttons on sheets.

Much better way would be using cells as if they are buttons. To use such button-like-cells, write codes this way (then copy and paste it to the sheet's code-editor for which you want to use the button-like-cells):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    a = Target.Row
    b = Target.Column
        If a >= 1 And a <= 5 And b = 1 Then
            MsgBox "Hello"
        ElseIf  a >= 1 And a <= 5 And b = 2 Then
            MsgBox "Good morning"
        End If
End Sub

This code enables you to use the button-like-cells. You can administrate many buttons at a same time this way. If you click one of cells from A1 to A5, a message box saying "Hello" appears. If you click one of cells from B1 to B5, a message box saying "Good morning" appears.

That is, all cells between A1 and A5 and all cells between B1 to B5 became buttons which show a message box when it is clicked. It is MUCH BETTER to button-like-cells to deal with many almost-same buttons than automatically adding many new ActiveX buttons and its codes by "Insert codes" things.

In addition, I will give you one more code that can be useful when dealing with many button-like-cells for a table.

lastRow = Worksheets("Sheet1").Range("A1").End(xlDown).Row

This variable "lastRow" shows the Row-number of the lowest one of cells that are existing under A1. Please note all cells must have the value to be counted by this code: empty cell is always considered as the end of row. (except if all cells are empty)

Using this code, we can fetch the lowest row of a table, that can be useful to deal with many button-like-cells for a table. For example:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    a = Target.Row
    b = Target.Column
    lastRow = Worksheets("Sheet1").Range("A1").End(xlDown).Row
    If a >= 1 And a <= lastRow And b = 1 Then
        MsgBox "Hello"
    ElseIf  a >= 1 And a <= lastRow And b = 2 Then
        MsgBox "Good morning"
    End If
End Sub

We can administrate all button-like-cells between A1 and the lowest row of a table at a same time. Or all button-like-cells between B1 and the lowest row of a table at a same time.