working with dynamically added command buttons and combo boxes

[Vikram D]

Hello,

I am trying to design a user form for data entry based on the worksheet column heads which have 34 columns. Somehow, I have managed to develop user form and assigning dynamic controls like text boxes, multiple combo boxes, labels referring to columns in the first row of a sheet. I also have dynamically added the command buttons using the array function.

The worksheet data loads to the user form with the last filled row on the user form activate event. However, I am not able to assign codes to the respective button-click events. I have tried various modes, but it didn’t work. Any help with codes for each button will help to complete my project.

The codes which I am using are as follows.

Dim BtnArr As Variant
BtnArr = Array("Add Entry", "Edit Entry", "Delete Entry", "Next Entry", "Previous Entry", "First Row", "Last Row", "New Entry")
StartPos = rMaxHeight + 10
WidthPos = 20
For k = LBound(BtnArr) To UBound(BtnArr)
Set btn = Controls.Add("Forms.CommandButton.1")
With btn
.Caption = BtnArr(k)
.Name = BtnArr(k)
.Left = WidthPos
.Top = StartPos
.Width = 80
End With
WidthPos = WidthPos + 150
Set objMyEventClass.btEvents = btn
'Set ButArray(k).btEvents = btn
Set btEvents = New Collection
Set btEvents.BtnArr = btn
btEvents.Add btEvent(k)
Next k
LoadData
End Sub
Private Function LoadData()
Dim cRow As Long
Dim ctr As MsForms.Control
Set wKS = ThisWorkbook.Worksheets("Vendor Master")
With wKS
i = 1
For Each ctr In frmVendorMaster.Controls
If TypeName(ctr) = "TextBox" Or TypeName(ctr) = "ComboBox" Then
ctr.value = .Cells(LastRow, i).value
i = i + 1
End If
Next
End With
End Function
The class module code
Option Explicit
Public WithEvents btEvents As MsForms.CommandButton
Public WithEvents MyCombo As MsForms.ComboBox
Private Sub Add_Entry_Click()
‘stuff to transfer user form data to last empty row
End Sub
Private Sub btEvents_EditEntry_click()
‘stuff to transfer user form data to the selected row
End Sub
Private Sub btnClose_Click()
Unload Me
End Sub

 

[vw412]

The issue is that you are trying to dynamically manage the userform when you don't need to. It is a complicated process to programmatically create userform controls and then add code to those controls. It involves accessing parts of Excel which are generally reserved for the VBE environment. If you already know the layout of the data (you say there are 34 columns) and you already know the layout of the controls on the userform and if these things are not subject to change then it seems that managing controls dynamically is an unnecessary complication to a simple task.

I suggest you design your userform in the VBE and eliminate the dynamic management of controls.

 

[Vikram D]

Thank you, will do accordingly.
I was just wondering why I wasn't able to assign codes rather why my class module events are not working.
The buttons were created dynamically and the event was working only for the last created button. There must have been something to look into just for learning purpose.
No matter, will design the user form in normal mode instead of these complications.
Many thanks!
Vikram D