Generating a new command button by clicking a command button and adding code to the new button.

[ExcelQs]

Hi. I've made a code that will create, position and name a new command button when a command button that already exisits is pushed. The next step is to insert code into the new button. I think the best way to do this is to put the new button code in a module, which can then be copied and pasted into the new button. I'm having trouble figuring that part out. Here is part of my code so far:

 '---Insert Button--------------------------------------------------
            Dim oleButton As OLEObject
            Dim bolSkip As Boolean
            Dim wsCvrSheet As Worksheet
            
            Set wsCvrSheet = Worksheets("Cover Sheet")
            
            'Turn screen updating off
            Application.ScreenUpdating = False
    
            'Check that button is not already entered
            For Each oleButton In wsCvrSheet.OLEObjects
                If oleButton.Name = "Unit 1" Then bolSkip = True
            Next
    
            'If Button does not exist
            If Not bolSkip Then
                'Pick cell where top right hand corner of button will be
                Dim rng As Range
                Dim ctop#, cleft#, cht#, cwdth#
                Set rng = Cells(1, 5)
                With rng
                    ctop = .Top
                    cleft = .Left
                    cht = .Height
                    cwdth = .Width
                End With
                
                With wsCvrSheet
                    'Add the button to the sheet with specified dimensions
                    Set oleButton = wsCvrSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1" _
                    , Link:=False, DisplayAsIcon:=False, Left:=cleft, Top:=ctop, Width:=3 * cwdth, _
                    Height:=2 * cht)
                End With
                
                '---Add code to button----
                '    Dim i As Long
                 '   Dim cmdArray() As New Class1
                    
                  '  i = 1
                    
                   ' ReDim Preserve cmdArray(1 To i)
                    'Set cmdArray(i).CmdEvents = oleButton
                '-----------------------------
                    
                'Rename the Button, so that the code added later refers to it
                oleButton.Name = "Unit 1"
                oleButton.Object.Caption = "Unit 1"
                
                Set rng = Nothing
                
                    
            Else
                MsgBox "Button already exists"
            End If
     
            'Turn screen updating on
            Application.ScreenUpdating = True
    
            Set wsCvrSheet = Nothing
            Set oleButton = Nothing
        'End Insert Button-------------------------------------------------------------

 

[tlowry]

Here is what I would do...

Sub makeButton(rng)
    On Local Error Resume Next
    Dim sname
    sname = Sheets("Cover Sheet").Buttons("Unit 1").Name
    If Err.Number <> 0 Then
        With rng
            Top = .Top
            cleft = .Left
            cht = .Height
            cwdth = .Width
        End With
        With Sheets("Cover Sheet").Buttons.Add( _
            Left:=cleft, Top:=ctop, Width:=3 * cwdth, Height:=2 * cht)
                    
            .OnAction = "Unit1_Click"
            .Characters.Text = "Unit 1"
            .Name = "Unit 1"
        End With
    End If
End Sub

To write code that is executed on click (OnAction):

Sub Unit1_Click()
    MsgBox Application.Caller & " was clicked"
End Sub

To create a button:

Sub TEST()
    makeButton Selection
End Sub

Let me know if this helps...