programmatically add commandbutton's code during runtime

nikobaresi

New Member
Joined
Oct 22, 2015
Messages
4
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Do you really need to dynamically create a userform for each user?

What do you actually have on each userform?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top