Is there a way to programatically add Form Controls?

Question

I'm working on a spreadsheet with a Form Control that opens a Userform used for data entry purposes. The submit button of the form fills a row of cells and adds two buttons on the last two cells of the row. It will insert as many rows as the user does and each time the two new buttons are created with their own distinct name. However, these are ActiveX controls and they are giving me compatibility problems with other Windows/Office versions once colleagues open the file and try to use it on their laptop.

This is the code I'm using to add one of the command buttons on the spreadsheet (it is essentially the same for the other button, just different variables):

Dim i As Long, Hght As Long
Dim Name As String, NName As String


i = 0
Hght = 305.25

NName = "cmdAction" & i

For Each OLEObject In ActiveSheet.OLEObjects
    If Left(OLEObject.Name, 9) = "cmdAction" Then
        Name = Right(OLEObject.Name, Len(OLEObject.Name) - 9)
        If Name >= i Then
            i = Name + 1
        End If
        NName = "cmdAction" & i
        Hght = Hght + 27
    End If
Next
  Dim UpdateEntry As OLEObject, N%

Set UpdateEntry = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
    , DisplayAsIcon:=False, Left:=Selection.Offset(0, 23).Left, Top:=Selection.Offset(0, 23).Top, Width:=72, Height _
    :=24)


UpdateEntry.Name = NName     
UpdateEntry.Object.Caption = "Edit Entry"
With ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
    N = .CountOfLines
.InsertLines N + 1, "Private Sub " & NName & "_Click()"
.InsertLines N + 2, vbTab & "Code for button goes here"
.InsertLines N + 3, vbTab & "End Sub"
End With

I was wondering if it was possible to do the same thing, but that the buttons created are form control instead of ActiveX?

The error that is displayed is Run-time error 32809: Application-defined or object-defined error. After extensive research, I've found that it happens due to the sheet getting corrupted once a different version of Windows has altered it. The only way to fix it is to create a new sheet, copying all of the contents to the new sheet, deleting the corrupted sheet and renaming the new one to the name it had previously. This works, but it is not possible to copy the ActiveX Controls, because they will be renamed and the appropriate code will not be in them, however the Form Control on the spreadsheet used to open the UserForm will work just fine, which is why I think my only solution would be to change all ActiveX to Form Control.

Would appreciate some help.

Never done anything like this specifically, but is this link any use? [link](http://www.ozgrid.com/Excel/free-training/ExcelVBA2/excelvba2lesson21.htm) Looks like you need object.Add — Clusks, Sep 01 '16 at 12:16
That sounds like a start I can look into it a bit more, many thanks. — user6782845, Sep 01 '16 at 14:59

score 0 · Answer 1 · answered Sep 01 '16 at 14:36

0

Instead of using ActiveX buttons, use Shapes and set the Shape.OnAction property to call the macro that you would normally place behind the ActiveX button. In the macro you can use Application.Caller to get the name of the shape that was clicked, thus allowing you to know which button was clicked and to branch your code accordingly.

answered Sep 01 '16 at 14:36

Excel Developers

2,785
2
21
35

Thanks for your reply, would the Shapes be linked to cells the same way that Form Controls are? If I select the whole sheet to copy it, the Form Controls get copied as well, but not the ActiveX ones. Would shapes get copied with cells too? – user6782845 Sep 01 '16 at 15:02
Yes, shapes would get copied. – Excel Developers Sep 02 '16 at 07:07

Is there a way to programatically add Form Controls?

1 Answers1