How to add a click event to a Finish button at run time in vba excel?

Question

I have made a userform in which it shows a loading screen when the loading is completed it stops and a finish button appears, but .... I have tried everything on my mind to assign an event to it that when clicked it closes the progress bar and a new userform which I have already created should appear I think its simple but I am not catching the problem help me catch the problem the code is below

Thanks in advance

Sub ProgressBar_Click()
Progress_Bar.Show
End Sub

Sub code()
Dim i As Integer, j As Integer, pctCompl As Single

Sheet1.Cells.Clear

For i = 1 To 100
    For j = 1 To 300
        Cells(5, 19).Value = j
    Next j
    pctCompl = i
    progress pctCompl
Next i

Dim cCont As control

Set cCont = Progress_Bar.Controls.Add _
            ("Forms.CommandButton.1", "CopyOf")

With cCont
    .Caption = "Finish"
    .AutoSize = True
    .Top = 84
    .Left = 258
    .Height = 18
    .Width = 66
End With

End Sub

Sub progress(pctCompl As Single)
    Progress_Bar.text.Caption = pctCompl & "% Completed"
    Progress_Bar.bar.Width = pctCompl * 2

    DoEvents

End Sub

Answer 1

You can use a class with a WithEvents button field to capture events from dynamically-added userform controls.

Here's a simple example:

--clsCB

Option Explicit

Public WithEvents CB As MSForms.CommandButton
Public frm As UserForm

Private Sub CB_Click()
    MsgBox "clicked button - " & CB.Caption
    Unload frm
End Sub

--UserForm

Option Explicit

Dim o As clsCB

Private Sub UserForm_Activate()
    Dim cCont As Control
    Set cCont = Me.Controls.Add("Forms.CommandButton.1", "CopyOf")
    With cCont
        .Caption = "Finish"
        .Top = 100
        .Left = 100
    End With
    Set o = New clsCB
    Set o.CB = cCont
    Set o.frm = Me
End Sub

Answer 2

I can find now way to assign code to a dynamically created button (which is what you are doing).

I would recommend making the button in Design mode and setting Visible=False. Then when ready make it visible.

Sub code()
Dim i As Integer, j As Integer, pctCompl As Single

Sheet1.Cells.Clear

For i = 1 To 100
    For j = 1 To 300
        Cells(5, 19).Value = j
    Next j
    pctCompl = i
    progress pctCompl
Next i

'Dim cCont As control

'Set cCont = Progress_Bar.Controls.Add _
'        ("Forms.CommandButton.1", "CopyOf")

'With cCont
'    .Caption = "Finish"
'    .AutoSize = True
'    .Top = 84
'    .Left = 258
'    .Height = 18
'    .Width = 66
'End With
CopyOf.Visible = True

End Sub

Private Sub CopyOf_Click()
Unload Me
End Sub