0

First of all I'm from Holland, so sorry for my bad English.

I have an excelsheet with a lot of records (from 10 to 1000, depending on the user). My idea was to dynamically make a userform where a row of textboxes per record is created. Then I want to make a commandbutton per record to change the data in the records. At last I add some code to the userform to define the actions executed when the commandbutton is clicked.

The textboxes and the commandbutton are visible when the userform is shown, but nothing happens when I click the commandbutton, despite the fact that the code is created at the userform.

I have an examplefile, but I don't know how to upload it here.

Underneath is the code (placed under the userform)

Private Sub UserForm_Initialize()
Dim cCntrl As Control
Dim txtB1 As Control
Dim cmb1 As Control

'Deleting lines doesn't work yet
'With ThisWorkbook.VBProject.VBComponents("EFRIS").CodeModule
'x = .countoflines
'y = Sheets("Data").Range("A3").Value
'If x > y Then .DeleteLines y, x - y
'x = .countoflines
'End With
'
'Sheets("data").Range("A3") = x

'locatie van de userform in het midden van het scherm plaatsen
'breedte van de userform aanpassen aan de breedte van het scherm
'hoogte van de userform aanpassen aan de hoogte van het scherm
a = Application.Width
b = Application.Height
EFRIS.Left = 5
EFRIS.Width = a - 25
EFRIS.Height = b - 50

'e = de .left van de eerste tekstbox
e = 100

'f = het aantal records in de sheet ("FRIS")
f = Sheets("FRIS").Range("B10000").End(xlUp).Row - 2

'a is de loop in rijen
For a = 1 To f
'b = de loop in kolommen
For b = 1 To 8
If b = 1 Then c = "a"
If b = 2 Then c = "b"
If b = 3 Then c = "c"
If b = 4 Then c = "d"
If b = 5 Then c = "e"
If b = 6 Then c = "f"
If b = 7 Then c = "g"
If b = 8 Then c = "h"
'If b = 9 Then c = "i"
'If b = 10 Then c = "j"

'breedte per textbox
If b = 1 Then d = 30
If b = 2 Then d = 100
If b = 3 Then d = 100
If b = 4 Then d = 130
If b = 5 Then d = 150
If b = 6 Then d = 150
If b = 7 Then d = 150
If b = 8 Then d = 50
'If b = 9 Then d = 100
'If b = 10 Then d = 30

Set txtB1 = Controls.Add("Forms.TextBox.1", "TB" & a & b, True)
With txtB1
    .Height = 20
    .Width = d
    .Left = e
    .Top = 5 + (a * 20)
    .Value = Sheets("FRIS").Range("A2").Offset(a, b)
    .Locked = True
End With

'.left van de textbox
e = e + d

Next
'E resetten naar 100
e = 100

Set cmb1 = Controls.Add("Forms.CommandButton.1")
With cmb1
    .Caption = "Wijzigen"
    .Height = 20
    .Width = 90
    .Left = 5
    .Top = 5 + (a * 20)
End With

With ThisWorkbook.VBProject.VBComponents("EFRIS").CodeModule
x = .countoflines
Sheets("Data").Range("A3") = x
'.InsertLines x + 1, "Private Sub " & cmb1.Name & "_Click()"
'.InsertLines x + 2, "AStart.show"
'.InsertLines x + 3, "Unload Me"
'.InsertLines x + 4, "End Sub"
End With


'g bepaald de totale hoogte van de userform voor de scrollbar
g = 20 + a * 20
Next

EFRIS.ScrollBars = fmScrollBarsVertical
EFRIS.ScrollTop = 0
EFRIS.ScrollHeight = g

EFRIS.Show

End Sub
Matthijs
  • 11
  • 3
  • 1
    possible duplicate of [Assign code to a button created dynamically using VBA on excel](http://stackoverflow.com/questions/10224511/assign-code-to-a-button-created-dynamically-using-vba-on-excel) – USFBS May 29 '15 at 12:42

2 Answers2

1

Assign code to a button created dynamically

The above may help you. You need to actually assign code to the button. There are 2 methods on how to do this in the link above.

Community
  • 1
  • 1
99moorem
  • 1,955
  • 1
  • 15
  • 27
  • @Matthijs I used this example to help me build a Userform that does exactly what you're looking for. This is good stuff – USFBS May 29 '15 at 12:41
0

You must add the code you want to run when clicking the button in the Sub CommandButton1_Click(), where CommandButton1 is the button name.

To create this Sub, double click on the button in the form designer.

The Sub UserForm_Initialize() is executed when the Form is loading.

Emmanuel P
  • 36
  • 3