Excel-VBA How to know which dynamically created UserForm TextBox has changed?

Question

I have an excel UserForm that creates textboxes during execution time. code as follows;

Dim CompHandler() As New CCompHandler
Dim tb As MSForms.TextBox   
Dim count As Integer
For i in Range(something)
    If i = anotherthing Then
        Set tb = UserForm1.Controls.Add("Forms.TextBox.1", "tb" & count)
        With tb
            .Width = iTbWidth
            .Top = count * distance
            .Left = iTbLeft
            .Height = iTbHeight
            .Value = Cells(row, column)
            ReDim Preserve CompHandler(0 To count)
            Set CompHandler(count).TextBoxGroup = tb
        End With
    count = count + 1
    End If
Next i

I want to write back the changed value to the corresponding cell. I'm already able to get when the box has changed and the new value with this code on a class called CCompHandler:

Option Explicit

Public WithEvents TextBoxGroup As MSForms.TextBox

Private Sub TextBoxGroup_Change()

    MsgBox TextBoxGroup

End Sub

So.. any ideas on how can I get which textbox has changed? Or maybe is there a better way of doing that? Thanks in advance

How is CompHandler defined? Is Componentes the user form? I cannot test or fiddle with this without making ALOT of guesses. Mind giving us the rest of the code? — Daniel
– Daniel, Commented Sep 17, 2012 at 13:45

Doug Glancy · Accepted Answer · 2012-09-17 14:00:19Z

3

The Tag property is typically used for something like this. On creation add something like:

With tb
  ...
  .Tag = i.Address
...
End With

You can then access the Tag property later, with something like:

Debug.Print tbWhoseValueHasChanged.Tag

Your code snippet has a lot of undefined/unclear variables, including i. I assumed it was a range variable in the above.

edited Sep 17, 2012 at 14:00

answered Sep 17, 2012 at 13:54

Doug Glancy

27.5k6 gold badges72 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ramtoo Over a year ago

Thank you! But how can I use that inside the class handler? Or is there a better way of using it?

Doug Glancy Over a year ago

You said you can get when the TextBox has changed, so just access its Tag property at that time. It's persistent. See edit.

ramtoo Over a year ago

It just worked. Thank you! I feel like it was just a silly question.

Collectives™ on Stack Overflow

Excel-VBA How to know which dynamically created UserForm TextBox has changed?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related