LSF


Hello,

I want to add new buttons to an existing userform at runtime and link event handler to them so that the buttons do something.

I've tried several ways that I've found online and managed to create the button and to programmatically add the code for the click event but when i click the button nothing happens. The button and click event sub code is created fine.

I have added the reference to Microsoft VBA Extensibility but something is missing.

Please, I am completely stuck and would appreciate any help! Thank you.

Here's the code I have so far:

Dim myCmdObj As CommandButton
Dim i As Integer

Private Sub CommandButton1_Click()

Dim NName As String

i = i + 1

' Set the name for the button
NName = "cmdAction" & i

' Add button
Dim N As Long
Set myCmdObj = frmMain.Controls.Add("Forms.CommandButton.1", NName, Visible)

With myCmdObj
.Caption = NName
.Left = 20 * i
.Width = 20
.Top = 20
End With

' Inserts code for the button
With ThisWorkbook.VBProject.VBComponents(5).CodeModule
N = .CountOfLines
.InsertLines N + 1, "Private Sub " & NName & "_Click()"
.InsertLines N + 2, vbNewLine
.InsertLines N + 3, vbTab & "lblTest.Caption = ""BUTTON CLICKED"""
.InsertLines N + 5, "End Sub"
End With
End Sub

Please help me!!




Re: Dynamically add controls with events at runtime

Andy Pope


Hi,

Is there any reason you can not add the button and event code at design and simply hide the control until needed






Re: Dynamically add controls with events at runtime

LSF

The reason that I am not creating the control at design time and hiding it until it is needed is because I don't know how many controls I'm going to need and I don't want to put an upper limit on how many can be created.

Also I discovered that if the subroutine is called by the nama eof the CommandButton variable NOT the commandbutton name, then it works.

But this implies that the declaration has to be global. For creating more than one control i would have to declare more than one CommandButton objects at design time. But then I'm still left with the same problem of how to create as many controls as needed at runtime without specifying an upper limit at design time.

For example:

Dim WithEvents NewButton as CommandButton 'Cannot declare this inside a procedure or error

private sub cmdAction_Click()

Set NewButton = Controls.Add("Forms.CommandButton.1")

NewButton.Name = "ButtonName"

end sub

Private Sub NewButton_Click()

lblTest.Caption = "THIS EVENT IS FIRED WHEN BUTTON IS CLICKED"
End Sub

Private Sub ButtonName_Click()

lblTest.Caption = "this event is NOT fired when button is clicked"
End Sub

It really doesn't make much sense that it would work like this, but that's how it is. What I need is a way to make the click event fire with the BUTTON.NAME attribute and NOT with the actual name of the control variable.

Can somebody please help me!! any input is appreciated!!






Re: Dynamically add controls with events at runtime

Andy Pope

Does this help.
http://j-walk.com/ss/excel/tips/tip76.htm

Or you could use a class with the event code in. See Stephen Bullen's reply in this post.
http://groups.google.com/group/microsoft.public.excel.programming/browse_thread/thread/93d77c52ee239ef9/a5831a94d1b49dcb lnk=st&q=&rnum=1&hl=en#a5831a94d1b49dcb





Re: Dynamically add controls with events at runtime

LSF

Yes, the second link is what I needed!

Now I can create multiple buttons at runtime and deal with them all with the same click event handler and refer to each of them individually using their name attribute. No need to add subprocedures to the event code programatically.

Thank you so much!





Re: Dynamically add controls with events at runtime

magicalclick

Cool, bookmark this thread. I was having the same problem. I ended up creating tons of the same buttons and keep copy and paste the same even handler. Although this still can be tricky when I run out of space for the buttons, but it already took care of the main problem. Thanks.



Re: Dynamically add controls with events at runtime

Anonymous3461

I was looking for a way to do exactly what you described in VBA. I was creating a list of different objects, each with an erase button next to it, so depending on how many objects there were (varies), I would need x number of erase buttons, each with the same event handler when clicked. The Second Link's example worked beautifully, thanks so much for a clear concise answer - saved me a lot of time!! Big Smile