programmatically add commandbutton's code during runtime

[nikobaresi]

Hi everybody,

Briefly, What i'm trying to do is a program that creates a userform for each customer I have in a database with all the products they ordered, the details of them and a commandbutton that will work as a check.
The userform helps me enlisting the orders.

As every customer order different products and quantities I programmed the macro with loops within my database in order to do the userforms with the corresponding caption, labels and commandbuttons, and that works correctly. But now I need to put the code of the commandbutton within the whole code.

thanks for your interest and help,


I post the whole code if you want to take a look, but many parts of the code are from other parts of the process:

Sub crearuserforms()


Worksheets("Hoja1").Activate
ActiveSheet.Range("A1:A" & rango + 1).AutoFilter Field:=1
Range("a1000000").Select
Selection.End(xlUp).Select
Range("a2", Selection).Select
filasprodpedidos = Selection.Rows.Count


'Identifies the customers and writes the number that correspond to each one

clientes


LastRow = Worksheets("Hoja1").Range("A" & Rows.Count).End(xlUp).Row
rango = Worksheets("Hoja1").Range("a1:a" & LastRow)


Worksheets("Hoja2").Activate
Range("a1000000").Select
Selection.End(xlUp).Select
Range("a1", Selection).Select
filasclientes = Selection.Rows.Count

For i = 1 To filasclientes
Cells(i, 2) = "UserForm" & i
nombrecliente = Worksheets("hoja2").Cells(i, 1).Value
Cells(i, 3).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(Hoja1!R1C[-2]:R" & filasprodpedidos + 1 & "C[-2], RC[-2])"


Next


For i = 1 To filasclientes


' generates the userforms


nombrecliente = Worksheets("hoja2").Cells(i, 1).Value


Set pedido1 = ThisWorkbook.VBProject. _
VBComponents.Add(3)
With pedido1
.Properties("Caption") = nombrecliente
.Properties("Width") = 650
.Properties("Height") = 40 + 27 * Worksheets("hoja2").Cells(i, 3).Value - 1
End With


Worksheets("Hoja1").Activate
Range("a100000").Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlUp)).Select
rango = Selection.Rows.Count


Selection.AutoFilter
ActiveSheet.Range("A1:A" & rango + 1).AutoFilter Field:=1, Criteria1:= _
nombrecliente


Range("b100000").Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
filacomienzo = ActiveCell.Row


Worksheets("Hoja1").Activate
Range("b100000").Select
Selection.End(xlUp).Select
filafinal = ActiveCell.Row



' ******************parts of the userform************

rango2 = filafinal - filacomienzo


For j = 1 To rango2




Worksheets("Hoja1").Activate
Set producto1 = pedido1.designer.Controls _
.Add("forms.label.1")
With producto1
.Caption = Cells(filacomienzo + j, 2).Value
.Left = 10
.Top = 10 + 30 * (j - 1)
.BackColor = vbWhite
.BorderStyle = 1
.BorderColor = vbBlack
.Width = 130
End With

Set cantidad1 = pedido1.designer.Controls _
.Add("forms.label.1")
With cantidad1
.Caption = Cells(filacomienzo + j, 3).Value
.Left = 150
.Top = 10 + 30 * (j - 1)
.BackColor = vbWhite
.BorderStyle = 1
.BorderColor = vbBlack
.Width = 40
End With

Set rangopeso1 = pedido1.designer.Controls _
.Add("forms.label.1")
With rangopeso1
.Caption = 0
.Left = 200
.Top = 10 + 30 * (j - 1)
.BackColor = vbWhite
.BorderStyle = 1
.BorderColor = vbBlack
.Width = 60
End With

Set comentarios1 = pedido1.designer.Controls _
.Add("forms.label.1")
With comentarios1
.Caption = Cells(filacomienzo + j, 4).Value
.Left = 270
.Top = 10 + 30 * (j - 1)
.BackColor = vbWhite
.BorderStyle = 1
.BorderColor = vbBlack
.Width = 230
End With


Set Listo1 = pedido1.designer.Controls _
.Add("Forms.CommandButton.1")
With Listo1
.Caption = "Listo1"
.Left = 550
.Top = 10 + 30 * (j - 1)
End With

ActiveSheet.Range("A1:A" & rango + 1).AutoFilter Field:=1

Next
Next




End Sub

 

[Norie]

Do you really need to dynamically create a userform for each user?

What do you actually have on each userform?

 

[nikobaresi]

Well, the database has the orders per week, so i have to create a userform for each customer of that week.

each userform has the name of the product(s), the quantity(ies) and the specifications with a commandbutton per product that I use to check when a product is already enlisted. I do this as an order can have as much as 30 products each, and the people responsible for this has had problems while enlisting the orders.

 

[Norie]

Couldn't you use a listbox to list the products and orders?

It could be set up so that each row has a checkbox next to it.

Or perhaps a combobox with a list of all products and a listbox where you can store selected products.