Code for a CommandButton created in runtime

[bradgar]

Hi All,

I have a UserForm that creates a CommandButton during runtime, how can I access or write code for a CommandButton_Click event for that button? I'm lost..

 

[bradgar]

Did that make sense or does no one know?

 

[meldoc]

Hello,

See the Link

 

[mikerickson]

If its just one button, you can use something like this in the uf code module

Public WithEvents newButton as msForms.CommandButton

' ...

Sub makeNewButton()
    set newButton = Me.Controls.Add(forms.CommandButton.1)
End Sub

Public Sub newButton_Click()
    MsgBox "the new button was clicked"
End Sub

 

[bradgar]

Thanks Mel and Mike,

Mel, I was hoping to do this without the Class modules as this is the only area I am unfamiliar with in VBA. It also makes this feature more complicated than I'd like.

Mike, this is more along the lines of what I was looking for however this didn't work, even though I know the button name it doesn't recognize the sub.

The button(s) are already created during runtime in the userform module by pressing another command button. It is on a multipage. There could also be more than one button created. The button(s) names are created in a For next statement and each consecutive buttons name is editB & i, where i increase by value of 1 for each next.

Not entirely sure how to handle this.

Thanks,

-B

 

[meldoc]

Hi again,

See if this helps, but note it is a complicated topic, whichever method you choose.

 

[mikerickson]

This worked for me. (Note the declaration of a Public module wide variable withevents)

Public WithEvents newControl As MSForms.CommandButton

Private Sub newControl_Click()
    MsgBox "I'm a new control"
End Sub

Private Sub UserForm_Click()
    Set newControl = Me.Controls.Add("forms.CommandButton.1")
    With newControl
        .Top = 10: .Left = 10
        .Height = 20: .Width = 67
        .Caption = "New"
    End With
End Sub

The problem with this approach is that it only works with one command button. It would be easier just to make a button and keep it invisble until needed.

If adding controls at run-time is really needed, the class approach is the most robust and the easiest to impliment and maintain.

Do all the created command buttons do essensialy the same thing?

 

[bradgar]

Thanks again guys!

It looks like I will have to figure out the Class Module method.

Mike, yes, the command buttons do exactly the same thing. For instance there could be 1 - 12 serial numbers. Each serial number is found in worksheet then UserForm creates multipage with each serial number on a diff. page(tab). Each page(tab) must look exactly the same so I use .pages(2).copy and .paste for each serial number.

Pages(0) - Pages(5) are the templates to copy for different information (button1 = receiving, button2=shipping, etc.). Pages(2) has a command button that it copies (could be once could be eleven times) and I want the copied buttons to do exactly the same as the original button they are copied from each time.

I have so much code in so many places on this one it's near impossible to try to post it up here. So thanks for the support and ideas guys, I really appreciate it!

-B

 

[bradgar]

I think I'm in over my head here guys.

I went with the class module method as follows..:

Class Module

Private WithEvents mobjBtn As MSForms.CommandButton
Private msOnAction As String
''// This has to be generic or call by name won't be able to find the methods
''// in your form.
Private mobjParent As Object


Public Property Get Object() As MSForms.CommandButton
    Set Object = mobjBtn
End Property


Public Function Load(ByVal parentFormName As Object, ByVal btn As MSForms.CommandButton, ByVal procedure As String) As DynBtn
    Set mobjParent = parentFormName
    Set mobjBtn = btn
    msOnAction = procedure
    Set Load = Me
End Function


Private Sub Class_Terminate()
    Set mobjParent = Nothing
    Set mobjBtn = Nothing
End Sub


Private Sub mobjBtn_Click()
    CallByName mobjParent, msOnAction, VbMethod
End Sub

Creating the Command Button from another Command Button:

Public Sub op1_Click()


Set fc = Sheet2.Columns("A").Find(What:=RGAN, After:=Range("A1"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
Set fc2 = Sheet3.Columns("A").Find(What:=RGAN, After:=Range("A1"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
Set F = Sheet5.Columns("A").Find(What:=RGAN, After:=Range("A1"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)


ed.Locked = True
p1.Locked = True
p2.Locked = True
p3.Locked = True
ed.BackStyle = fmBackStyleTransparent
p1.BackStyle = fmBackStyleTransparent
p2.BackStyle = fmBackStyleTransparent
p3.BackStyle = fmBackStyleTransparent


logS = Sheet5.Range("G" & F.Row).Value


If Application.UserName Like "*Brad*" Then
    usR = "BG"
ElseIf Application.UserName Like "*Michael*" Then
    usR = "MS"
ElseIf Application.UserName Like "*Ron*" Then
    usR = "RO"
ElseIf Application.UserName Like "*Jennifer*" Then
    usR = "JN"
ElseIf Application.UserName Like "*Anil*" Then
    usR = "AP"
ElseIf Application.UserName Like "*Scott*" Then
    usR = "SP"
End If


logS = logS & Chr(13) & Chr(13) & "----------" & Now & "----------" & Chr(13) & usR & ": Customers Details Button Pressed"
Sheet5.Range("G" & F.Row).Value = logS


With MultiPage1
    .Pages(0).Visible = False
    .Pages(2).Visible = False
    .Pages(3).Visible = False
    .Pages(4).Visible = False
    .Pages(5).Visible = False
 End With


OpDe.Caption = "Customer Details"




Label9.Caption = numberofmodules
Aserialnumbers = Sheet3.Range("H" & fc2.Row).Value


nm = numberofmodules - 1
np = numberofmodules


With MultiPage1
        If .Pages(4).Visible = True Then
            .Pages.Remove (4)
            GoTo nXt0
        End If
        For i = 1 To nm
            If i <> 0 Then
                .Pages.Remove (6)
            End If
        Next i
nXt0:
    .Pages(1).Visible = True
End With


If numberofmodules.Value = 1 Then
    If Aserialnumbers <> "" Then
        snV = Aserialnumbers
    Else
        snV = "Not Received"
    End If
ElseIf numberofmodules.Value >= 2 Then
    If Aserialnumbers = "" Then
        snV = "Not Received"
    ElseIf Aserialnumbers <> "" Then
        For i = 0 To nm
            snV = Sheet3.Range("T" & fc2.Row + i).Value
            If i >= 1 Then
                MultiPage1.Pages.Add , snV
                p1 = Sheet2.Range("J" & fc.Row).Value
                p2 = Sheet2.Range("K" & fc.Row).Value
                p3 = Sheet2.Range("L" & fc.Row).Value
                ed = Sheet5.Range("E" & F.Row + i).Value
                boF1.Caption = i + 1 & " of"
                
                With MultiPage1
                    .Pages(1).Controls.Copy
                    With .Pages(i + 5)
                        .Paste
                        .Controls("TextBox1").Name = "p1" & i
                        .Controls("TextBox2").Name = "p2" & i
                        .Controls("TextBox3").Name = "p3" & i
                        .Controls("TextBox4").Name = "ed" & i
                        .Controls("CommandButton1").Name = "editB" & i
                        Sheet5.Range("H2").Value = i + 1
                    End With
                End With
                ReDim mBtn(i) As DynBtn
                Set mBtn(i) = New DynBtn
                mBtn(i).Load(Me, Me.Controls("editB" & i), "DoSomething").Object = True
            End If
        Next i
        With MultiPage1.Pages(1)
            .Caption = Sheet3.Range("T" & fc2.Row).Value
            p1 = Sheet2.Range("J" & fc.Row).Value
            p2 = Sheet2.Range("K" & fc.Row).Value
            p3 = Sheet2.Range("L" & fc.Row).Value
            ed = Sheet5.Range("E" & F.Row).Value
            boF1 = "1 of"
        End With
    End If
End If
MultiPage1.Value = 1


End Sub

Finally the Call to Procedure I want the Command Button(s) to Run:

Public Sub DoSomething()
Set fc = Sheet2.Columns("A").Find(What:=RGAN, After:=Range("A1"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
Set fc2 = Sheet3.Columns("A").Find(What:=RGAN, After:=Range("A1"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
Set F = Sheet5.Columns("A").Find(What:=RGAN, After:=Range("A1"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)


logS = Sheet5.Range("G" & F.Row).Value


If Application.UserName Like "*Brad*" Then
    usR = "BG"
ElseIf Application.UserName Like "*Michael*" Then
    usR = "MS"
ElseIf Application.UserName Like "*Ron*" Then
    usR = "RO"
ElseIf Application.UserName Like "*Jennifer*" Then
    usR = "JN"
ElseIf Application.UserName Like "*Anil*" Then
    usR = "AP"
ElseIf Application.UserName Like "*Scott*" Then
    usR = "SP"
End If


logS = logS & Chr(13) & Chr(13) & "----------" & Now & "----------" & Chr(13) & usR & ": Edit / Change button pressed"
Sheet5.Range("G" & F.Row).Value = logS
i = Sheet5.Range("H2").Value
    
    If Me.Controls("editB" & i).Caption Like "Save Changes" Then
        'For i = 1 To Sheet2.Range("H" & fc.Row).Value
            With Me
            edT = Sheet5.Range("E" & F.Row).Value
            p1T = Sheet2.Range("J" & fc.Row).Value
            p2T = Sheet2.Range("K" & fc.Row).Value
            p3T = Sheet2.Range("L" & fc.Row).Value
            .Controls("ed" & i).Locked = True
            .Controls("p1" & i).Locked = True
            .Controls("p2" & i).Locked = True
            .Controls("p3" & i).Locked = True
            Sheet2.Range("J" & fc.Row).Value = .Controls("p1" & i)
            Sheet2.Range("K" & fc.Row).Value = .Controls("p2" & i)
            Sheet2.Range("L" & fc.Row).Value = .Controls("p3" & i)
            Sheet5.Range("E" & F.Row).Value = .Controls("ed" & i)
            .Controls("ed" & i).BackStyle = fmBackStyleTransparent
            .Controls("p1" & i).BackStyle = fmBackStyleTransparent
            .Controls("p2" & i).BackStyle = fmBackStyleTransparent
            .Controls("p3" & i).BackStyle = fmBackStyleTransparent
            
            If edT = .Controls("ed" & i) And p1T = .Controls("p1" & i) And p2T = .Controls("p2" & i) And p3T = .Controls("p3" & i) Then
                logS = logS & Chr(13) & Chr(13) & "----------" & Now & "----------" & Chr(13) & usR & ": Edit Pressed with no change"
                Sheet5.Range("G" & F.Row).Value = logS
            ElseIf edT <> ed Then
                logS = logS & Chr(13) & Chr(13) & "----------" & Now & "----------" & Chr(13) & usR & ": Changes made" & Chr(13) & "From: " & ed & Chr(13) & "To: " & edT
                Sheet5.Range("G" & F.Row).Value = logS
            ElseIf p1T <> p1 Then
                logS = logS & Chr(13) & Chr(13) & "----------" & Now & "----------" & Chr(13) & usR & ": Changes made" & Chr(13) & "From: " & p1 & Chr(13) & "To: " & p1T
                Sheet5.Range("G" & F.Row).Value = logS
            ElseIf p2T <> p2 Then
                logS = logS & Chr(13) & Chr(13) & "----------" & Now & "----------" & Chr(13) & usR & ": Changes made" & Chr(13) & "From: " & p2 & Chr(13) & "To: " & p2T
                Sheet5.Range("G" & F.Row).Value = logS
            ElseIf p3T <> p3 Then
                logS = logS & Chr(13) & Chr(13) & "----------" & Now & "----------" & Chr(13) & usR & ": Changes made" & Chr(13) & "From: " & p3 & Chr(13) & "To: " & p3T
                Sheet5.Range("G" & F.Row).Value = logS
            End If
            
            .Controls("editB" & i).Caption = "Edit / Change"
        End With
        'Next i
        Exit Sub
        
    End If
    
    If Me.Controls("editB" & i).Caption Like "Edit / Change" Then
        
        'For i = 1 To Sheet2.Range("H" & fc.Row).Value
        With Me
            .Controls("ed" & i).Locked = False
            .Controls("p1" & i).Locked = False
            .Controls("p2" & i).Locked = False
            .Controls("p3" & i).Locked = False
            
            .Controls("ed" & i).BackStyle = fmBackStyleOpaque
            .Controls("p1" & i).BackStyle = fmBackStyleOpaque
            .Controls("p2" & i).BackStyle = fmBackStyleOpaque
            .Controls("p3" & i).BackStyle = fmBackStyleOpaque
            .Controls("editB" & i).Caption = "Save Changes"
        End With
        'Next i
        Exit Sub
        
    End If


End Sub

This worked perfectly when this procedure created one button. When it creates three or more of these buttons I get an error (at the red text), which really stops in the Procedure I am trying to run (at the green text). Orange text is the important part of this Command Button click event (Op1) which is the actual creation of the new Command Button(s).

I have no idea where to go, or even why I get this error.. "Run Time Error 5: Invalid Procedure or Call"

Thanks for any help in advance!

-B

 

[mikerickson]

That looks awfully generic. I'm not sure if CallByName is an Excel VBA command (it looks like Excel VBA's Application.Run). In any case, this example works for me.

For this example, start off by creating a userform with a multi-page and a command button (outside of the multi-page). Press that button and a command button will be added to each page of the multi-page.
Press any of those run-time buttons and a message will appear telling you which page the clicked button is on and what its name is.

Create a class module, name it clsRunTimeCommandButton and put this code in that class module

' in class module named clsRunTimeCommandButton

Public WithEvents btnObject As MSForms.CommandButton

Property Get ufParent() As Object
    Set ufParent = btnObject.Parent
    On Error Resume Next
    Do
        Set ufParent = ufParent.Parent
    Loop Until Err
    On Error GoTo 0
End Property

Private Sub btnObject_Click()
    Call ufParent.MadeButtonRoutine(btnObject)
End Sub

Private Sub Class_Terminate()
    Set btnObject = Nothing
End Sub

Each clsRunTimeCommandButton has two properties.

btnObject (read/write) is the command button that was created.
ufParent (read only) is the userform on which that button is.

The Terminate event releases the custom object's object variables.
More about the btnObject_Click event later.

In the userform's code module put this code.

' in userform's code module

Dim myCreatedButtons As Collection

Private Sub CommandButton1_Click()
    Dim i As Long
    Dim NewCustomButtonObject As clsRunTimeCommandButton
    Dim newButton As MSForms.CommandButton
    
    With Me.MultiPage1
        For i = 0 To .Pages.Count - 1
            Set newButton = .Pages(i).Controls.Add("Forms.CommandButton.1")
            
            With newButton
                Rem assign appearance
                .Top = 5: .Left = 5
                .Height = 20: .Width = 83
                .Caption = "Button for page " & (i + 1)
            End With
            
            Set NewCustomButtonObject = New clsRunTimeCommandButton: Rem new instance of custom object
            Set NewCustomButtonObject.btnObject = newButton: Rem asssign newly created button to that object
            myCreatedButtons.Add Item:=NewCustomButtonObject: Rem put it in the collection
        Next i
    End With
    
    Set newButton = Nothing
    Set NewCustomButtonObject = Nothing
End Sub

Public Sub MadeButtonRoutine(clickedButton As MSForms.CommandButton)
    MsgBox clickedButton.Caption & vbCr & clickedButton.Name
End Sub

Private Sub UserForm_Initialize()
    Set myCreatedButtons = New Collection
End Sub

Private Sub UserForm_Terminate()
    Dim oneMadeButton As clsRunTimeCommandButton
    For Each oneMadeButton In myCreatedButtons
        Set oneMadeButton = Nothing
    Next oneMadeButton
    Set myCreatedButtons = Nothing
End Sub

The module wide myCreatedButtons collection holds the buttons created at run-time.

The CommandButton1_Click event runs when the (design time) button is clicked. It loops through each page of the multi-page, creates a new command button, adjusts the postition and caption of that button, creates a new instance of the custom class clsRunTimeCommandButton and assigns the newly created command button to that object's btnObject property. It then puts that new instance into the myCreatedButtons collection.

The Userform_Initialize and Terminate events are housekeeping related to the collection and releasing the created custom objects.

When a created button is clicked, the btnObject_Click event in the class module is run.

That event then calls the sub MadeButtonRoutine in the userform code module, passing the command button as an argument. Note that MadeButtonRoutine is declared as Public.

One could put all that code into the class module, in the btnObject_Click event, but I prefer to refer back to the userform so I don't have to keep switching between userform module and class module while debugging.

Note: there is no need to change the names of the created buttons.

I hope this helps.

Alternately (I keep trying to find ways to avoid making run time controls) have you considered making one button outside of the multi-page that tests which page is active to determine what to do.