Creating several buttons from a list in runtime

[blukrr]

Hey guys,

I managed to create a script to create a several buttons from a list of names of buttons. I did this so I could easily change the name and caption of them. I also need to add Sub routines to those buttons, they are actually going to load the Caption name in a variable that I will use later in my program.

For the most part my program is working. But the buttons won't do anything when I use a .CodeModule to insert VB scripts for them like this one below:

[VBA]Sub Button1_Click()

Msgbox "Hello!"

End Sub
[/VBA]

here is the full code:
[VBA]Private Sub CommandButton1_Click()

Dim ListSize
Dim CurrentName As String
Dim CurrentButtonList
Dim CurrentCodes As Range
Dim btn As CommandButton
Dim objForm As Object
Set objForm = ThisWorkbook.VBProject.VBComponents("UserForm1")

CurrentButtonList = 1

For Each CurrentCode In Range("Codes")

Set btn = UserForm1.Controls.Add("Forms.CommandButton.1", CurrentCode, True)
With btn
.Caption = CurrentCode.Offset(0, 1)
.Left = 80
.Width = 80
.Height = 20
.Top = 20 * CurrentButtonList
End With

With objForm.CodeModule
Line = .CountOfLines
.InsertLines Line + 1, "Private Sub " & CurrentCode & "_Click()"
.InsertLines Line + 2, "MsgBox ""Hello!"""
.InsertLines Line + 3, "End Sub"
End With
CurrentButtonList = CurrentButtonList + 1

Next CurrentCode
End Sub[/VBA]
By the way I using a Msgbox just to make sure they work.

This part works ok:
[VBA] With objForm.CodeModule
Line = .CountOfLines
.InsertLines Line + 1, "Private Sub " & CurrentCode & "_Click()"
.InsertLines Line + 2, "MsgBox ""Hello!"""
.InsertLines Line + 3, "End Sub"
End With [/VBA]

The only thing that doesn't it's the buttons that don't do anything.
To test this script you need to create some lines like

Button1
Button2
Button3
Button4
Button5
Button6

in colum A (for example) select it and name this range "Codes".

And then create another on the right side with the Captions for those buttons.

There is a bug in the program that I'm going to have to deal with later, because everytime you click the button that generates the list of buttons, it also creates a bunch of Subs for those buttons inside the Userform Module. If you click 2 times it will creates 2 Subs with the same name, and this crashed the program.

Thanks guys for the help.

[GTO]

...The only thing that doesn't it's the buttons that don't do anything...

I don't believe it is possible to do this as you are trying, as I do not think you can write an event handler into an already "running" module and have anything happen.

While adding controls on the fly is possible, unless you are resizing the form and have some built-in limit... well, this would seem likely to be problematic. Why not just add the maximum number of buttons before hand? You can still change captions, visibility, resize the form as needed, etc.

See here for an example of a temp userform and controls, but please note that the code is all being plunked in before the form actually initializes.

Example

Hope that helps,

Mark

[Teeroy]

I tried using this technique once and gave up in disgust. The reason to use it was not worth the aggravation as the errors can sometimes appear random. As GTO suggests I used a prebuilt userform and hid what I didn't need.

I tried your sample code and found that the click events generated were not consistent; they were recognised in the object browser but not the in the userform code module. I think GTO is on the money with the supplied example; you can't modify the code attached to an object that is compiled before runtime (opinion). If you really need to do this I would try to build the userform from scratch within the code (I found some snippets that suggest this may work).

If you get it to work please post it. I would like to see it as there are far more questions than useful answers out there on this topic.

Good luck.

[blukrr]

As GTO suggests I used a prebuilt userform and hid what I didn't need.

Why not just add the maximum number of buttons before hand? You can still change captions, visibility, resize the form as needed, etc.

Thanks for the replies, I didn't really thought about that, I think it will be a lot easier and less prone for bugs if I do it the way you both are suggesting.

they were recognised in the object browser but not the in the userform code module

I also noticed that, they don't appear from the dropdown menu where you select the Sub you want to work on when the code is inserted using codemodule.

I really appreciate all the help on the subject. I'll follow your advice.

[blukrr]

Hey guys,

I searched a bit more on the subject and found this topic which has the solution.

[vba]
The code for adding a button at run time and then to add events is truly as simple as it is difficult to find out..I can say that because I have spent more time on this perplexity and got irritated more than in anything else I ever programmed ..
Create a Userform and put in the following code:
Option Explicit


Dim ButArray() As New Class2

Private Sub UserForm_Initialize()
Dim ctlbut As MSForms.CommandButton

Dim butTop As Long, i As Long

'~~> Decide on the .Top for the 1st TextBox
butTop = 30

For i = 1 To 10
Set ctlbut = Me.Controls.Add("Forms.CommandButton.1", "butTest" & i)

'~~> Define the TextBox .Top and the .Left property here
ctlbut.Top = butTop: ctlbut.Left = 50
ctlbut.Caption = Cells(i, 7).Value
'~~> Increment the .Top for the next TextBox
butTop = butTop + 20

ReDim Preserve ButArray(1 To i)
Set ButArray(i).butEvents = ctlbut
Next
End Sub
[/VBA]

Now U need to add a Class Module to your Code for the project..Please remember its class module not Module.And put in following simple Code( In my case the class name is Class2)-

[VBA]
Public WithEvents butEvents As MSForms.CommandButton

Private Sub butEvents_click()

MsgBox "Hi Shrey"

End Sub
[/vba]
Btw credits goes to DaveShaw from Stackoverflow.

Thanks for the help.

[Teeroy]

Hi Blukrr,

Thanks for posting this. When you think about it it does make sense as the class object doesn't exist until it's instantiated. If you wanted to write code programatically to its events I guess you would have to do it before you DIM the object.

[snb]

I'd prefer to use VBA for performing (calculating, filtering, selecting etc.) not for designing. Designing is what you should do beforehand.
I see no advantage at all in designing at runtime; it only costs unnecessary processor time you could better spend to minimize waiting times for a user.

[blukrr]

Actually I'm just using this code because I know I'm going to have several buttons to deal with later, and it going to be like a database. I'll have to change those buttons often (caption, name) and it's going to make my life easier If I manage to do it this way, I'm not talking about 10 buttons, but most likely 100 or so.