blukrr
09-04-2012, 06:33 PM
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:
Sub Button1_Click()
Msgbox "Hello!"
End Sub
here is the full code:
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
By the way I using a Msgbox just to make sure they work.
This part works ok:
With objForm.CodeModule
Line = .CountOfLines
.InsertLines Line + 1, "Private Sub " & CurrentCode & "_Click()"
.InsertLines Line + 2, "MsgBox ""Hello!"""
.InsertLines Line + 3, "End Sub"
End With
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.
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:
Sub Button1_Click()
Msgbox "Hello!"
End Sub
here is the full code:
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
By the way I using a Msgbox just to make sure they work.
This part works ok:
With objForm.CodeModule
Line = .CountOfLines
.InsertLines Line + 1, "Private Sub " & CurrentCode & "_Click()"
.InsertLines Line + 2, "MsgBox ""Hello!"""
.InsertLines Line + 3, "End Sub"
End With
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.