Ive successfully used Jafar's code for the capturing of change events of dynamic textboxes. See Here
But i cant seem to get the chnage event to run on the textboxes.
Here is my code for my userform
Here is my 'addlabel' code
Here is my Class code
My textboxes arent named TextBox1, TextBox2... etc they are named txtdim1, txtdim2, etc..
Any help would be appreciated
But i cant seem to get the chnage event to run on the textboxes.
Here is my code for my userform
VBA Code:
Private Sub UserForm_Activate()
'DEBUGGING
addLabel
End Sub
Private Sub UserForm_AddControl(ByVal Control As MsForms.Control)
Static oCol As Collection
Dim oTextBoxEvents As CTextBoxEvents
'hook the added textboxes events.
If TypeOf Control Is MsForms.TextBox Then
Set oTextBoxEvents = New CTextBoxEvents
oTextBoxEvents.SetControlEvents(Control) = True
If oCol Is Nothing Then
Set oCol = New Collection
End If
oCol.Add oTextBoxEvents
End If
End Sub
Here is my 'addlabel' code
Code:
Public meas As Long
Sub addLabel()
Set PD = Sheets("ProgramData")
Dim theLabel As Object
Dim labelCounter As Long
col = 3
'DEBUGGING
Set Title = UserForm1.Controls.Add("Forms.Label.1", "Part", True)
With Title
.Caption = PD.Cells(meas, 1)
.Left = 10
.Top = 10
.FontSize = 18
End With
For labelCounter = 1 To PD.Cells(meas, 2)
Set theLabel = UserForm1.Controls.Add("Forms.Label.1", "labdim" & labelCounter, True)
With theLabel
.Caption = Format(PD.Cells(meas, col), "0.00")
.Left = 10
.Width = 50
.Top = 34 + 20 * labelCounter
.Height = 20
End With
col = col + 3
Next
col = 5
For labelCounter = 1 To PD.Cells(meas, 2)
Set theLabel = UserForm1.Controls.Add("Forms.Label.1", "labdimlow" & labelCounter, True)
With theLabel
.Caption = Format(PD.Cells(meas, col), "0.00")
.Left = 50
.Width = 50
.Top = 34 + 20 * labelCounter
.Height = 20
End With
col = col + 3
Next
For labelCounter = 1 To PD.Cells(meas, 2)
Set theLabel = UserForm1.Controls.Add("Forms.TextBox.1", "txtdim" & labelCounter, True)
With theLabel
.Left = 75
.Width = 70
.Top = 30 + 20 * labelCounter
.Height = 17
End With
Next
col = 4
For labelCounter = 1 To PD.Cells(meas, 2)
Set theLabel = UserForm1.Controls.Add("Forms.Label.1", "labdimup" & labelCounter, True)
With theLabel
.Caption = Format(PD.Cells(meas, col), "0.00")
.Left = 150
.Width = 50
.Top = 34 + 20 * labelCounter
.Height = 20
End With
col = col + 3
Next
UserForm1.Show 0
End Sub
Public Sub Tester()
Dim CTRL
Dim i As Long
For Each CTRL In UserForm1.Controls
i = i + 1
Cells(i, 1).Value = CTRL.Name
Next CTRL
End Sub
Here is my Class code
Code:
Option Explicit
Private Type GUID
Data1 As Long
Data2 As Integer
Data3 As Integer
Data4(0 To 7) As Byte
End Type
#If VBA7 Then
Private Declare PtrSafe Function IIDFromString Lib "ole32.dll" (ByVal lpsz As LongPtr, lpiid As GUID) As Long
Private Declare PtrSafe Function ConnectToConnectionPoint Lib "shlwapi" Alias "#168" (ByVal punk As stdole.IUnknown, ByRef riidEvent As GUID, ByVal fConnect As Long, ByVal punkTarget As stdole.IUnknown, ByRef pdwCookie As Long, Optional ByVal ppcpOut As LongPtr) As Long
#Else
Private Declare Function IIDFromString Lib "ole32.dll" (ByVal lpsz As Long, lpiid As GUID) As Long
Private Declare Function ConnectToConnectionPoint Lib "shlwapi" Alias "#168" (ByVal punk As stdole.IUnknown, ByRef riidEvent As GUID, ByVal fConnect As Long, ByVal punkTarget As stdole.IUnknown, ByRef pdwCookie As Long, Optional ByVal ppcpOut As Long) As Long
#End If
Private oTextBox As Object
Public Property Let SetControlEvents(ByVal TextBox As Object, ByVal SetEvents As Boolean)
Const S_OK = &H0
Static lCookie As Long
Dim tIID As GUID
Set oTextBox = TextBox
If IIDFromString(StrPtr("{00020400-0000-0000-C000-000000000046}"), tIID) = S_OK Then
Call ConnectToConnectionPoint(Me, tIID, SetEvents, TextBox, lCookie)
If lCookie Then
Debug.Print "Connection set for: " & TextBox.Name
Else
Debug.Print "Connection failed for: " & TextBox.Name
End If
End If
End Property
Public Sub OnEnter()
'Attribute OnEnter.VB_UserMemId = &H80018202
Debug.Print "[ENTER EVENT] " & oTextBox.Name & vbTab & "Value: " & vbTab & oTextBox.Value
End Sub
Public Sub OnExit(ByVal Cancel As MsForms.ReturnBoolean)
' Attribute OnExit.VB_UserMemId = &H80018203
Debug.Print "[EXIT EVENT] " & oTextBox.Name & vbTab & "Value: " & vbTab & oTextBox.Value
End Sub
Public Sub BeforeUpdate(ByVal Cancel As MsForms.ReturnBoolean)
'Attribute BeforeUpdate.VB_UserMemId = &H80018201
Debug.Print "[BEFORE_UPDATE EVENT] " & oTextBox.Name & vbTab & "Value: " & vbTab & oTextBox.Value
End Sub
Public Sub AfterUpdate()
'Attribute AfterUpdate.VB_UserMemId = &H80018200
Debug.Print "[AFTER_UPDATE EVENT] " & oTextBox.Name & vbTab & "Value: " & vbTab & oTextBox.Value
End Sub
My textboxes arent named TextBox1, TextBox2... etc they are named txtdim1, txtdim2, etc..
Any help would be appreciated