Put data from generated textboxes to specific cells

Krosta8 · Sep 26th 2017

Hello guys,

I need a little help with my code.
The final version of the code will be for renaming of column's headers through Userform.
I have a code to automatically generate TextBoxes of every used column (only the first row for each column) and insert the values to them.

Then I generated another (blank) TextBoxes, where I want to insert text and the text should rewrite original column's headers.

I don't know how to address to this generated TextBoxes.

Any help would be appreciated.

Here is my code:

Code

Private Sub TextBoxGenerate()
Dim lRow As Long, Ndx As Long
Dim tBox As MSForms.TextBox
Dim tBox2 As MSForms.TextBox
Dim lastColumn As Long
Const dDistHoriz As Double = 3
lastColumn = List1.Cells(1, Columns.Count).End(xlToLeft).Column
            For Ndx = 1 To lastColumn
                Set tBox = Me.Controls.Add(bstrProgID:="forms.textbox.1", Name:="tBoxEventsID", Visible:=True)
                With tBox
                    .Width = 60
                    .Height = 20
                    .Left = 10
                    .Top = (Ndx - 0.8) * (30 + dDistHoriz) + 30
                    .Text = Range("A1").Offset(0, Ndx - 1).Value
                End With
            Next
            For Ndx = 1 To lastColumn
                Set tBox2 = Me.Controls.Add(bstrProgID:="forms.textbox.1", Name:="tBoxEventsID", Visible:=True)
                With tBox2
                    .Width = 60
                    .Height = 20
                    .Left = tBox.Width + 25
                    .Top = (Ndx - 0.8) * (30 + dDistHoriz) + 30
                    Range("A3").Offset(0, Ndx - 1).Value = .Text
                End With
            Next
Me.Height = 400
Me.Width = tBox.Width + tBox2.Width + 58 + 50
Me.ScrollBars = fmScrollBarsVertical
Me.ScrollHeight = tBox.Top + tBox.Height + 5
End Sub




Sub ChangeColumnHeader_Click()
Dim lastColumn As Long
lastColumn = List1.Cells(1, Columns.Count).End(xlToLeft).Column
Controls("tBox2").Value = Cells(1, 3)
End Sub

Display More

[ATTACH=CONFIG]74025[/ATTACH]

sysss · Sep 27th 2017

Re: Put data from generated textboxes to specific cells

Hello,

use index number to name dynamically made textboxes and then refer to them in the loop like in the following:

Code

Private Sub TextBoxGenerate()
    Dim lRow As Long, Ndx As Long
    Dim tBox As MSForms.TextBox
    Dim tBox2 As MSForms.TextBox
    Dim lastColumn As Long
    Const dDistHoriz As Double = 3
 
    lastColumn = List1.Cells(1, Columns.Count).End(xlToLeft).Column
    For Ndx = 1 To lastColumn
        Set tBox = Me.Controls.Add(bstrProgID:="forms.textbox.1", Name:="tBoxEventsID", Visible:=True)
        With tBox
            .Width = 60
            .Height = 20
            .Left = 10
            .Top = (Ndx - 0.8) * (30 + dDistHoriz) + 30
            .Text = Range("A1").Offset(0, Ndx - 1).Value
        End With
    Next
    For Ndx = 1 To lastColumn
        Set tBox2 = Me.Controls.Add(bstrProgID:="forms.textbox.1", Name:="txtNewName" & Ndx, Visible:=True)
        With tBox2
            .Width = 60
            .Height = 20
            .Left = tBox.Width + 25
            .Top = (Ndx - 0.8) * (30 + dDistHoriz) + 30
            Range("A3").Offset(0, Ndx - 1).Value = .Text
        End With
    Next
    Me.Height = 400
    Me.Width = tBox.Width + tBox2.Width + 58 + 50
    Me.ScrollBars = fmScrollBarsVertical
    Me.ScrollHeight = tBox.Top + tBox.Height + 5
End Sub
 
 
Sub ChangeColumnHeader_Click()
    Dim lastColumn As Long
Dim  Ndx As Long
 Dim cTB As Control
       
    lastColumn = List1.Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To lastColumn
Set cTB = Me.Controls("txtNewName" & Ndx)
If Not cTB.Text = "" Then
List1.Cells(1, Ndx) = cTB.Text
End If
Next i
End Sub

Display More

s.

sysss · Oct 1st 2017

Re: Put data from generated textboxes to specific cells

Quote from sysss;800852

Hello,

Code

Sub ChangeColumnHeader_Click()
    Dim lastColumn As Long
Dim  Ndx As Long
 Dim cTB As Control
       
    lastColumn = List1.Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To lastColumn
Set cTB = Me.Controls("txtNewName" & Ndx)
If Not cTB.Text = "" Then
List1.Cells(1, Ndx) = cTB.Text
End If
Next i
End Sub

Display More

s.

Just found a mistake in the code... Forgot to replace variable i by variable Ndx... Corrected below:

Code

Sub ChangeColumnHeader_Click()
    Dim lastColumn As Long
Dim  Ndx As Long
 Dim cTB As Control
       
    lastColumn = List1.Cells(1, Columns.Count).End(xlToLeft).Column
For Ndx = 1 To lastColumn
Set cTB = Me.Controls("txtNewName" & Ndx)
If Not cTB.Text = "" Then
List1.Cells(1, Ndx) = cTB.Text
End If
Next Ndx
End Sub

Display More

Regards,
s.

**mikerickson** · Oct 1st 2017

Re: Put data from generated textboxes to specific cells

When you make the text box, put the address of the cell it refers to in the .Tag property.

Code

With tBox 
            .Width = 60 
            .Height = 20 
            .Left = 10 
            .Top = (Ndx - 0.8) * (30 + dDistHoriz) + 30 
            .Text = Range("A1").Offset(0, Ndx - 1).Value 
            .Tag = Range("A1").Offset(0, Ndx - 1).Address(,,,True)
        End With

Then when you write to the cell

Code

With someTextBox
    Range(.Tag).Value = .Text
End With

Krosta8 · Nov 3rd 2017

I would like to thank you. I didn't know about the reaction on this topic, because I was three weeks away from everything and then I didn't see the notification. I figured it out on myself and it is almost the same code, like you suggested.

Thank you very much, for your help and sorry for this very late respond.

Put data from generated textboxes to specific cells

Participate now!

Share