Initialize Multiple Textboxes

[Ethan]

Hello,

I got about 60 textboxes on a userform.
I am wondering if there is a way to initialize them all at once instead of writing some code for each of them.

the range is A4:I9
Textbox 1 is A4 Textbox 2 is A5 etc.

[Ethan]

BTW, what about entering them can this also be done in a smarter way

iso

Private Sub TextBox1_Change()
Worksheets("ToDo").Range("A4").Value = TextBox1.Value
End Sub
Private Sub TextBox2_Change()
Worksheets("ToDo").Range("B4").Value = TextBox2.Value
End Sub
Private Sub TextBox3_Change()
Worksheets("ToDo").Range("C4").Value = TextBox3.Value
End Sub
Private Sub TextBox4_Change()
Worksheets("ToDo").Range("D4").Value = TextBox4.Value
End Sub
Private Sub TextBox5_Change()
Worksheets("ToDo").Range("E4").Value = TextBox5.Value
End Sub
Private Sub TextBox6_Change()
Worksheets("ToDo").Range("F4").Value = TextBox6.Value
End Sub

[benny]

Hi, Ethan,

Put this code behind the UserForm.

Private Sub UserForm_Initialize()
Dim n As Long
For n = 1 To 60
Me.Controls("TextBox" & n) = Range("A4:J9")(n)
Next n
End Sub

Good luck.

[Ethan]

Thank you Benny,
That works great!

What about my second question, is there a smart way for entering them.
Instead of writing some new code for each textbox

[benny]

Hi, Ethan,

To your second question: see attachment.

[Ethan]

Awesome Benny!
You helped me a lot!

How can I format the textboxes to show the results as time.
The cells themselves are already formatted as time.

I already tried something but this does not work.


Dim lLoop As Long
For lLoop = 3 To 9
UserForm1.Controls("TextBox" & lLoop).value = "hh:mm"
Next lLoop

[benny]

Ethan,

Give this a try.

[Ethan]

It works fine in the example you provided, but I can't get it work in mine?

[Ethan]

The file I uploaded was protected :banghead:
This one is ok

[mikerickson]

You could make a custom class called Class1 with this code in a class module.
'in class module

Public WithEvents CommonTextBox As MSForms.TextBox

Private Sub CommonTextBox_Change()
With CommonTextBox
Range(.Tag) = .Text
End With
End Sub
and then put code like this in the UserForm's code module

Private Sub UserForm_Initialize()
Dim oneCommonBox As Class1
Dim sourceRange As Range
Dim i As Long

Set sourceRange = Sheet1.Range("A4:i9")

For i = 1 To 12
Set oneCommonBox = New Class1
With oneCommonBox
Set .CommonTextBox = Me.Controls("TextBox" & i)
With .CommonTextBox
.Tag = sourceRange.Item(i).Address(, , , True)
.Text = CStr(sourceRange.Item(i).Value)
End With
End With
myCommonBoxes.Add oneCommonBox
Next i
End Sub

Private Sub UserForm_Terminate()
Set myCommonBoxes = Nothing
End Sub

[benny]

Ethan,

Format Range(Ä4:I9") as Text.
See attachment.
It works for me.

[Ethan]

Hello MikeRickson,
Thank you for helping, your code gives an error on the following line:
"Object Required"

myCommonBoxes.Add oneCommonBox


To be honest, I find your code quite hard to comprehend.
I am still a beginner at VBA.
I prefer Benny's approach.

Hello Benny,
Although not shown in the sample I provided, there is a lot of calculating done with the timings and percentages.
Formatting them to text, will create errors allover.
Trying to change all these formulas is a hopeless task.

[mikerickson]

My mistake, I forgot the declaration line.

' in userform code module

Dim myCommonBoxes As New Collection

Private Sub UserForm_Initialize()
Dim oneCommonBox As Class1
Dim sourceRange As Range
Dim i As Long

Set sourceRange = Sheet1.Range("A4:i9")

For i = 1 To 12
Set oneCommonBox = New Class1
With oneCommonBox
Set .CommonTextBox = Me.Controls("TextBox" & i)
With .CommonTextBox
.Tag = sourceRange.Item(i).Address(, , , True)
.Text = CStr(sourceRange.Item(i).Value)
End With
End With
myCommonBoxes.Add oneCommonBox
Next i
End Sub

Private Sub UserForm_Terminate()
Set myCommonBoxes = Nothing
End Sub

[Ethan]

Thanks Mikerickson,
I got your code working now, but like Benny's the formatting remains a problem.

Hello Benny,
Any change you cab think of a solution to this problem?

[frank_m]

a stab in the dark
.Value = Format(SourceRange.Item(i).Value, "hh:mm")

[benny]

Ethan,

The Range("A4:B9") does not correspond with
the numbering of the TextBoxes 1 to 12.

[Bob Phillips]

Use this



Private Sub UserForm_Initialize()
Dim oneCommonBox As Class1
Dim sourceRange As Range
Dim i As Long

Set sourceRange = Worksheets("ToDo").Range("A4:I9")

For i = 1 To 12
Set oneCommonBox = New Class1
With oneCommonBox
Set .CommonTextBox = Me.Controls("TextBox" & i)
With .CommonTextBox
.Tag = sourceRange.Item(i).Address(, , , True)
.Text = CStr(sourceRange.Item(i).Text)
End With
End With
myCommonBoxes.Add oneCommonBox
Next i
End Sub