Returning changed dynamically created textbox value to corresponding cell

[frateg8r]

I have a userform that dynamically creates textboxes based on a sheet's data:
Userform

Option Explicit
Private AddedTextBoxes As New Collection

Sub UserForm_Activate()

    Dim rCell As Range
    Dim rTextBox_CntrNum As MSForms.TextBox
    Dim rTextBox_CntrSz As MSForms.TextBox
    Dim offsetHeight As Double
    For Each rCell In ActiveWorkbook.Sheets("CONTAINERS").Range("A8:A99")
        If rCell.value > 0 Then
            With Me.Controls
                'ESTABLISH CONTAINER# TEXTBOX - CONTROLS TOTAL NUMBER OF ROWS & SIZE OF USERFORM
                Set rTextBox_CntrNum = .Add("Forms.Textbox.1")
                With rTextBox_CntrNum
                    .value = rCell.value
                    .Top = 25 + offsetHeight
                    offsetHeight = offsetHeight + .Height
                    .Left = 10
                    .Width = 80
                    .Font = "Andale Mono"
                    .Font.Size = 10
                    .TextAlign = fmTextAlignCenter
                    .SelectionMargin = False
                End With
                'ESTABLISH CNTR SIZE TEXTBOX
                Set rTextBox_CntrSz = .Add("Forms.Textbox.1")
                With rTextBox_CntrSz
                    .value = rCell.Offset(0, 1).value
                    .Top = 7 + offsetHeight
                    .Left = 95
                    .Width = 30
                    .Font = "Andale Mono"
                    .Font.Size = 10
                    .TextAlign = fmTextAlignCenter
                    .SelectionMargin = False
                End With
            Me.Height = offsetHeight + 61 'HEADER ROW = 61
        End If
    Next rCell

    'TEXTBOX CHANGE EVENT HANDLER
    Dim txtBoxHandler As TextBoxEventHandler, c As Control
     For Each c In Me.Controls
        If TypeOf c Is MSForms.TextBox Then
            Set txtBoxHandler = New TextBoxEventHandler
            Set txtBoxHandler.Control = c
            AddedTextBoxes.Add txtBoxHandler
        End If
    Next


End Sub

ClassModule

Private WithEvents MyTextBox As MSForms.TextBox

Public Property Set Control(tb As MSForms.TextBox)
    Set MyTextBox = tb
End Property


Private Sub MyTextBox_Change()
    Dim rCell As Range
    If MyTextBox.value <> "" Then
        Debug.Print MyTextBox.Name & " " & MyTextBox.value
    End If
End Sub

The code works fine to this point; what I need it do, however, is return any changes made to the textbox to the appropriate cell. In other words:

If MyTextBox.value <> "" Then
     'At this point, the event handler has detected changes in the textbox. The resulting MyTextBox.value needs to go back to the cell from which the original data (or null value, as the case may be) originated.
End If

I've got a functional event handler for checkboxes on another dynamic userform which works fine, but those are limited to a single column range, not a range of cells over multiple columns. I've experimented with a number of variations to no effect, and also perused a number of articles and posts looking for a solution. I will continue, but in the meantime, if anyone can point me in the right direction, I'd appreciate it.

Thanks
Bob

 

[frateg8r]

I believe I have solved my own question with this, which I believe is an inelegant yet functional solution:

Private Sub MyTextBox_Change()
    sTxtBoxNum = wf.Substitute(MyTextBox.Name, "TextBox", "")
    sTxtBoxFactor = Right(sTxtBoxNum, 1)
    If sTxtBoxNum < 11 Then
        sCellNumFactor = 8
    Else
        sCellNumFactor = 8 + ((sTxtBoxNum - sTxtBoxFactor) / 10)
    End If
    Select Case sTxtBoxFactor
        Case 1
            Workbooks(strfilenum).Sheets("CONTAINERS").Range("A" & sCellNumFactor).value = MyTextBox.value
        Case 2
            Workbooks(strfilenum).Sheets("CONTAINERS").Range("B" & sCellNumFactor).value = MyTextBox.value
    End Select
End Sub

There are actually 10 text boxes per row of data. Since they generate as TextBox1 through TextBox10 per row, I can easily identify which row the particular textbox belongs to by calling it's name, then use that information as part of the destination cell for the data.

 

[frateg8r]

I believe I have solved my own question with this, which I believe is an inelegant yet functional solution:

Private Sub MyTextBox_Change()
    sTxtBoxNum = wf.Substitute(MyTextBox.Name, "TextBox", "")
    sTxtBoxFactor = Right(sTxtBoxNum, 1)
    If sTxtBoxNum < 11 Then
        sCellNumFactor = 8
    Else
        sCellNumFactor = 8 + ((sTxtBoxNum - sTxtBoxFactor) / 10)
    End If
    Select Case sTxtBoxFactor
        Case 1
            Workbooks(strfilenum).Sheets("CONTAINERS").Range("A" & sCellNumFactor).value = MyTextBox.value
        Case 2
            Workbooks(strfilenum).Sheets("CONTAINERS").Range("B" & sCellNumFactor).value = MyTextBox.value
    End Select
End Sub

There are actually 10 text boxes per row of data. Since they generate as TextBox1 through TextBox10 per row, I can easily identify which row the particular textbox belongs to by calling it's name, then use that information as part of the destination cell for the data.

Unfortunately, this does not work. It seems the dynamic textbox names can vary depending on variables such as other open userforms or the active worksheet. Additionally, further testing has revealed that even when the variable naming can be accounted for, only 1 character of a textbox value change is effectively being rewritten to the cell. Barring a viable solution and due to the need to get the uf into production, it looks like I will have to scrap the ability to change the textbox values, and instead go to the worksheet to do it.