Change worksheet cell value when userform textbox value changes on real time

[Akanjana]

i have added few dynamic Textboxes to userform with run time on userfrom activate. The Taxtboxes are named as txt1 , txt 2 , txt 3 an so on
i want to change worksheet cell A1 value when txt1 value changes and change worksheet cell B1 value when txt2 value changes and so on.

i do not want to achieve it with following by adding change event for each textbox, as textboxes are added on run time with add control.

Private sub txt1_change()
worksheets("database").range("A1"),value = txt1.value
End sub

So i tried other methods as follows but no success.

I added static Textbox in userform and named it as "txtid'

Private Sub UserForm_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) ' to get value in txtid of textbox when user start typing in textbox but no success.
txtid.value = ActiveControl.Name  'to get name of selected textbox
End Sub


Private sub txtid_change()
Dim a As integer
Dim update UpdateValue As String
a = 1
Updatevalue = "txt" & a  'txt is txtbox starting name string

for n = 1 to 15
worksheets("database").cells(1, 1 + a).value = Updatevalue
next n
a = a + 1

End sub

i want to update the value in worksheet on real time when user changes textbox value it in userform.


i am not expert with vba.

 

[FracinDean]

Most form controls have a property that can link them to a cell in a worksheet. Can you assign this property to the cells when the text box controls are added?

 

[Akanjana]

Most form controls have a property that can link them to a cell in a worksheet. Can you assign this property to the cells when the text box controls are added?

For userform textbox control their are two properties i.e control source and Text . i don't know to how it will work to change cell value. can you please give me few steps to achieve it.

 

[FracinDean]

I don't think I'm the best to tell you how to code it, but I think "control source" is the one you want to use. I know how to edit the property from the properties box in the VBA editor, but I don't know how to code it. You could start a new thread called "Updating the Control Source Property Dynamically in Code" or something like that, or just do a search on that and see what you will find.

 

[Fluff]

Please do NOT start a new thread for this question. Thanks

 

[Akanjana]

on my search i got following codes. so these codes are for options. can i utilize following code for textbox and update cell value with change event.

Class code

Option Explicit
Public WithEvents ctlOptionButton As MSForms.OptionButton

Private Sub ctlOptionButton_Change()
    If ctlOptionButton.Value Then
        MsgBox ctlOptionButton.Name & " is selected"
    End If
End Sub

userform code

Option Explicit

Dim aOptionButtons() As clsOptionButton


Private Sub UserForm_Initialize()
    Dim oControl As Object, iCounter As Long
   
    Me.OptionButton1.Value = True
   
    ReDim aOptionButtons(1 To Me.Controls.Count)
    For Each oControl In Me.Controls
        If TypeName(oControl) = "OptionButton" Then
            iCounter = iCounter + 1
            Set aOptionButtons(iCounter) = New clsOptionButton
            Set aOptionButtons(iCounter).ctlOptionButton = oControl
        End If
    Next
    ReDim Preserve aOptionButtons(1 To iCounter)
End Sub

 

[Akanjana]

Please do NOT start a new thread for this question. Thanks

@Fluff can you please help me with this.

 

[Fluff]

Fraid not. I've never used dynamically created controls, because I always found them to be a pain.

 

[Akanjana]

Fraid not. I've never used dynamically created controls, because I always found them to be a pain.

you are correct, i am in pain now.