Code for command button created during runtime does not work [Excel VBA]

Question

I need to create the code for CommandButton that will be created during run-time. This command button is dynamic because it based on the user data.

User_From code

Private Sub UserForm_Activate()

Dim ctlTXT As Control

For RevNo = 1 To RevCounter

    Set ctlTXT = Me.Controls.Add("Forms.CommandButton.1")

    ctlTXT.name = RevNo
    ctlTXT.Caption = Sheet4.Range("D" & RevNo + 4).value
    ctlTXT.Left = 18
    ctlTXT.Height = 18: ctlTXT.Width = 72
    ctlTXT.Top = 15 + ((RevNo - 1) * 25)    
Next

Me.Height = (RevNo * 17) + 50

ReDim Preserve cmdArray(1 To RevNo)
Set cmdArray(RevNo).CmdEvents = ctlTXT
Set ctlTXT = Nothing

End Sub

Class module Code

Private Sub CmdEvents_Click()

Dim i As Integer

i = CmdEvents.name

RevisionFormPrevious.LblResponsible.Caption = Sheet4.Range("C" & i +4).value
RevisionFormPrevious.LblEdition.Caption = Sheet4.Range("D" & i + 4).value
RevisionFormPrevious.LblTelNo.Caption = Sheet4.Range("E" & i + 4).value
RevisionFormPrevious.LblFeatures.Caption = Sheet4.Range("D" & i + 4).value
RevisionFormPrevious.Features.value = Sheet4.Range("F" & i + 4).value

Load RevisionFormPrevious
RevisionFormPrevious.Show

End Sub

The problem is, if there are more than one button created, the code only works for the last button created. When the first and second button clicked, nothing happened.

Where is RevCounter declared, populated and what is its' value when called? — Mark Fitzgerald
– Mark Fitzgerald, Commented Apr 10, 2017 at 10:17
What is the name of your Class ? In your Class code you need to add at the top Public WithEvents CmdEvents As MSForms.CommandButton, Then, in your Sub you need to define it using Dim ctlTXT As As MSForms.CommandButton — Shai Rado
– Shai Rado, Commented Apr 10, 2017 at 10:42
@MarkFitzgerald RevCounter is declared as public in another module. The value when it is call is the total number of Revision available — mfo_28
– mfo_28, Commented Apr 10, 2017 at 10:56
@ShaiRado al ready declared. Sorry I did not include it in the question. Already change the ctlTXT, nothing works. — mfo_28
– mfo_28, Commented Apr 10, 2017 at 10:57
You should be adding the objects to the array inside your loop, not after it. — Rory
– Rory, Commented Apr 10, 2017 at 11:02

Variatus · Accepted Answer · 2017-04-10 10:30:17Z

2

Each CommandButton requires its own event procedure which includes its name. In order to create this procedure you need access to the VBA-Project which is highly discouraged because any hacker could take control of your computer through that door. Therefore you have two ways to handle the problem.

Create as many CommandButtons as you might possibly need, each one with its own event procedure. Hide the buttons you don't immediately need and unhide and reposition them where your code now creates them.
Create only one button with only one event procedure but assign different values to its Caption or, possibly, Tag properties. Then program your event procedure to do different things depending upon what the Caption or Tag is when the button is clicked.

answered Apr 10, 2017 at 10:30

Variatus

14.4k2 gold badges16 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mfo_28 Over a year ago

Thank you for the solution. It will be complicated to follow the first option since I don't know how many should be created since will depend on the users. Its working now.

Collectives™ on Stack Overflow

Code for command button created during runtime does not work [Excel VBA]

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related