0

I am trying to create the code for the command button created at runtime. The command button created based on the dynamic input. I found the solution here

Assign code to a button created dynamically

The problem is this code is used for one command button only, I have several command button created at runtime. This is my code.

For i = 1 To RevCounter

Set ctlTXT = Me.Controls.Add("Forms.CommandButton.1")
ctlTXT.name = "Rev" & i
ctlTXT.Caption = Sheet4.Range("D" & i + 5).value
ctlTXT.Left = 18
ctlTXT.Height = 18: ctlTXT.Width = 72
ctlTXT.Top = 15 + ((i - 1) * 25)

Next

The question is, how to assign the code for command button created at runtime. For example if RevCount=5, there will be 5 command buttons created at runtime. I need to assign the code for each of the command buttons created.

Community
  • 1
  • 1
mfo_28
  • 23
  • 1
  • 6
  • not sure what is your question ? you want to know how to assign multiple `Sub` code to array of `CommandButton` ? where is the list of values you want ? – Shai Rado Apr 05 '17 at 12:07
  • @ShaiRado I want to assign the code for each of the command button created at runtime. If the RevCounter=5, so 5 command buttons will be created and 5 codes is needed. – mfo_28 Apr 05 '17 at 12:11
  • Have you tried [ctlTXT.OnAction = "nameOfMacroToRun"](http://stackoverflow.com/a/2783413/1153513)? – Ralph Apr 05 '17 at 12:13
  • @Ralph I tried already but im not sure whether the code doesn't work or I wrote the wrong code. Can you give me the example – mfo_28 Apr 05 '17 at 12:40

1 Answers1

0

The process you want to use is to:

  • add the button in a sheet

  • add the related code using the sub buttonname_click() format

You have the first part, so we'll deal with the second here. The code needs to be added to the relevant worksheet's code module; so this code would add code to the active sheet's code module, which is likely what you need.

Sub addcode()
    Dim subtext As String
    subtext = "Sub PrintStuff" & vbCrLf & "msgbox ""Hello World""" & vbCrLf & "End Sub"
    With Workbooks(ThisWorkbook.Name).VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
        .InsertLines .CountOfLines + 1, subtext
    End With
End Sub

There's not really enough information in your question to make this scaleable: what do you want the buttons to do? Can you add the sub that you'd like to add?

Preston
  • 4,628
  • 3
  • 33
  • 58
  • The command button is in the user form, what I want to do is, when the user click on the created button, it will show another form with the correct information for the clicked button – mfo_28 Apr 05 '17 at 12:42