Jon von der Heyden
MrExcel MVP, Moderator
- Joined
- Apr 6, 2004
- Messages
- 10,907
- Office Version
- 365
- Platform
- Windows
Hi
I am using table driven forms controls on userforms. E.g.:
I use the following (work in progress) function to add the controls to the userform (usually added to page or frame).
Now I need a means of trapping the controls events. I thought I could use a class, e.g.:
cFormEvents
It seems I cannot reference the controls because I they are added at runtime. For the given example, I want to run whatever procedure name appears for lblPrimaryContact in column AC (click event). So in my userform module I instantiate the class, but I get an error when I try and reference the control:
Error is "method or data member not found".
Can anyone suggest an alternative method to grab the click event for the control added at runtime?
I am using table driven forms controls on userforms. E.g.:
Excel 2010 | |||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | |||
1 | UI Name | MultiPage Parent | Page Parent | Control | Name | Top | Left | Height | Width | Caption | Tag | ControlTipText | SpecialEffect | WordWrap | MultiLine | BorderStyle | BorderColor | BackColor | BackStyle | ForeColor | ColumnCount | ColumnWidths | ListStyle | MultiSelect | Sort | Filter | Primary | Source | Click | Right-Click | Double-Click | View | Edit | ||
2 | CustomerHeader | mpMain | 1 | Forms.Label.1 | lblCustomerID | 0 | 1 | 12 | 54 | Customer ID | Click to Sort | 0 | TRUE | 1 | 6 | 12566463 | TRUE | TRUE | TRUE | ||||||||||||||||
3 | CustomerHeader | mpMain | 1 | Forms.Label.1 | lblCustomerName | 0 | 55 | 12 | 110 | Customer Name | Click to Sort | 0 | TRUE | 1 | 6 | 12566463 | TRUE | TRUE | FALSE | ||||||||||||||||
4 | CustomerHeader | mpMain | 1 | Forms.Label.1 | lblTradingName | 0 | 165 | 12 | 110 | Trading Name | Click to Sort | 0 | TRUE | 1 | 6 | 12566463 | TRUE | TRUE | FALSE | ||||||||||||||||
5 | CustomerHeader | mpMain | 1 | Forms.Label.1 | lblLegal Name | 0 | 275 | 12 | 110 | Legal Name | Click to Sort | 0 | TRUE | 1 | 6 | 12566463 | TRUE | TRUE | FALSE | ||||||||||||||||
6 | CustomerHeader | mpMain | 1 | Forms.Label.1 | lblAddress1 | 0 | 385 | 12 | 100 | Address 1 | 0 | TRUE | 1 | 6 | 12566463 | FALSE | TRUE | FALSE | |||||||||||||||||
7 | CustomerHeader | mpMain | 1 | Forms.Label.1 | lblAddress2 | 0 | 485 | 12 | 100 | Address 2 | 0 | TRUE | 1 | 6 | 12566463 | FALSE | TRUE | FALSE | |||||||||||||||||
8 | CustomerHeader | mpMain | 1 | Forms.Label.1 | lblCity | 0 | 585 | 12 | 75 | City | Click to Sort | 0 | TRUE | 1 | 6 | 12566463 | TRUE | TRUE | FALSE | ||||||||||||||||
9 | CustomerHeader | mpMain | 1 | Forms.Label.1 | lblPostCode | 0 | 660 | 12 | 60 | Post Code | Click to Sort | 0 | TRUE | 1 | 6 | 12566463 | TRUE | TRUE | FALSE | ||||||||||||||||
10 | CustomerHeader | mpMain | 1 | Forms.Label.1 | lblRegion | 0 | 720 | 12 | 75 | Region | Click to Sort | 0 | TRUE | 1 | 6 | 12566463 | TRUE | TRUE | FALSE | ||||||||||||||||
11 | CustomerHeader | mpMain | 1 | Forms.Label.1 | lblCountry | 0 | 795 | 12 | 75 | Country | Click to Sort | 0 | TRUE | 1 | 6 | 12566463 | TRUE | TRUE | FALSE | ||||||||||||||||
12 | CustomerHeader | mpMain | 1 | Forms.Label.1 | lblWebsite | 0 | 870 | 12 | 75 | Website | 0 | TRUE | 1 | 6 | 12566463 | FALSE | TRUE | FALSE | |||||||||||||||||
13 | CustomerHeader | mpMain | 1 | Forms.Label.1 | lblIndustry | 0 | 945 | 12 | 65 | Industry | Click to Sort | 0 | TRUE | 1 | 6 | 12566463 | TRUE | TRUE | FALSE | ||||||||||||||||
14 | CustomerHeader | mpMain | 1 | Forms.Label.1 | lblActive | 0 | 1010 | 12 | 45 | Active | Click to Sort | 0 | TRUE | 1 | 6 | 12566463 | TRUE | TRUE | FALSE | ||||||||||||||||
15 | CustomerHeader | mpMain | 1 | Forms.ListBox.1 | lbxCustomerHeader | 12 | 0 | 200 | 1056 | Right-click for contextual menu | 0 | 1 | 6 | 16777215 | 13 | 54;110;110;110;100;100;75;60;75;75;75;65;45 | 0 | 0 | |||||||||||||||||
16 | CustomerHeader | mpMain | 1 | Forms.Label.1 | lblPrimaryContact | 217 | 1 | 12 | 85 | > Primary Contact | Click to see Primary Contact details | 0 | TRUE | 0 | 16777215 | 0 | 16711680 | ||||||||||||||||||
17 | CustomerHeader | mpMain | 1 | Forms.Label.1 | lblCommercialContact | 217 | 86 | 12 | 85 | > Commercial Contact | Click to see Commercial Contact details | 0 | TRUE | 0 | 16777215 | 0 | 16711680 | ||||||||||||||||||
18 | CustomerHeader | mpMain | 1 | Forms.CommandButton.1 | cbtAddEdit | 239 | 1 | 19 | 48 | Add/Edit | Click to Add or Edit Customer | 16777215 | |||||||||||||||||||||||
UI |
I use the following (work in progress) function to add the controls to the userform (usually added to page or frame).
Code:
Public Function AddControls(ByVal objTarget As Object, ByVal strUiName As String)
Dim rngControls As Excel.Range, rngProperties As Excel.Range
Dim rngControl As Excel.Range, rngProperty As Excel.Range
Dim objControl As Object
With shtFormUI
Set rngControls = .Range("D2:D" & .Range("D" & .Rows.Count).End(xlUp).Row)
End With
For Each rngControl In rngControls
With rngControl
If .Offset(0, -3).Value = strUiName Then
Set objControl = objTarget.Controls.Add(.Value)
Set rngProperties = Intersect(shtFormUI.Range("E:X"), .EntireRow)
For Each rngProperty In rngProperties
With rngProperty
If .Value <> "" Then
Call CallByName(objControl, shtFormUI.Cells(1, .Column).Value, VbLet, .Value)
End If
End With
Next rngProperty
If TypeName(objControl) = "ListBox" Then
'testing only - retrieve records using SQL recordset
objControl.List = Sheet1.Range("A2:M3").Value
End If
End If
End With
Next rngControl
End Function
Now I need a means of trapping the controls events. I thought I could use a class, e.g.:
cFormEvents
Code:
Option Explicit
Public WithEvents lblLabel As MSForms.Label
Public WithEvents tbxTextBox As MSForms.TextBox
Public WithEvents cbxComboBox As MSForms.ComboBox
Public WithEvents lbxListBox As MSForms.ListBox
Public WithEvents cbtCommandButton As MSForms.CommandButton
Private Sub lblLabel_Click()
Dim strProcName As String
On Error Resume Next
strProcName = Application.VLookup(lblLabel.Object.Name, shtFormUI.Range("E:AC"), 29, False)
On Error GoTo 0
If strProcName <> "" Then
Call Application.Run(ThisWorkbook.Name & "!" & strProcName)
End If
End Sub
It seems I cannot reference the controls because I they are added at runtime. For the given example, I want to run whatever procedure name appears for lblPrimaryContact in column AC (click event). So in my userform module I instantiate the class, but I get an error when I try and reference the control:
Code:
Set m_clsFormEvents.lblLabel = Me.lblPrimaryContact
Error is "method or data member not found".
Can anyone suggest an alternative method to grab the click event for the control added at runtime?