Forms - Loop thru text boxes

  • Thread starter Thread starter Michael Beckinsale
  • Start date Start date
M

Michael Beckinsale

Hi All,

I have a userform with some 100 text boxes on it.

On initialization l apply the values to the text boxes using this code
(extract only)

Private Sub UserForm_Initialize()

Sheets("List_CellsLids").Select
Range("N2").Select

With frmAccom

tbUnit1.Text = ActiveCell.Value
tbUnit2.Text = ActiveCell.Offset(1, 0).Value
tbUnit3.Text = ActiveCell.Offset(2, 0).Value
tbUnit4.Text = ActiveCell.Offset(3, 0).Value
tbUnit5.Text = ActiveCell.Offset(4, 0).Value
tbUnit6.Text = ActiveCell.Offset(5, 0).Value
tbUnit7.Text = ActiveCell.Offset(6, 0).Value
tbUnit8.Text = ActiveCell.Offset(7, 0).Value
tbUnit9.Text = ActiveCell.Offset(8, 0).Value
tbUnit10.Text = ActiveCell.Offset(9, 0).Value
End With
End Sub

On 'Enter' l would normally enter the following code (extract only) so that
if the user 'edits' a value it is written to the appropriate cells

ActiveCell.Value = tbUnit1.Text
ActiveCell.Offset(1, 0).Value = tbUnit2.Text
ActiveCell.Offset(2, 0).Value = tbUnit3.Text
ActiveCell.Offset(3, 0).Value = tbUnit4.Text
ActiveCell.Offset(4, 0).Value = tbUnit5.Text
ActiveCell.Offset(5, 0).Value = tbUnit6.Text
ActiveCell.Offset(6, 0).Value = tbUnit7.Text
ActiveCell.Offset(7, 0).Value = tbUnit8.Text
ActiveCell.Offset(8, 0).Value = tbUnit9.Text
ActiveCell.Offset(9, 0).Value = tbUnit10.Text

However because l have to do this for so many text boxes and l have several
more to write l was hoping to apply the values using a vba / loop code
something like the following

Dim tbni As Integer

Private Sub cmdEnter_Click()
'Sheets("List_CellsLids").Select
'Range("N2").Select
tbni = 1
For i = 1 To 10
ActiveCell.Value = "tbUnit" & tbni & ".Value"
ActiveCell.Offset(1, 0).Activate
tbni = tbni + 1
Next

I think the answer is somewhere in the syntax. Can anybody help please?

Regards

Michael Beckinsale
 
For i = 1 To 10
ActiveCell.Offset(i-1,0).Value = Userform1.Controls("tbUnit" & i).Value
Next
 
Tom,

Many thanks. Works fine.

Another quick question re the same form - if you dont mind

When initializing the form l add up the values in the textboxes named
tbUnit1 to tbUnit20 and put the result in tbUnittotal. However if the user
'edits' a textbox the revised total does not show. Is there a way to show
the correct totals dynamically ?

Regards

Michael beckinsale
 
You would have to trap the entry and perform the calculation. There is not
way to link a sum formula to it. Even if you did it with cell links, the
sum formula would get overwritten.

You can use a technique documented by John Walkenbach to handle multiple
controls with a single event by using a class module.

Although written for command buttons, it works for textboxes as well. The
only constraint is that they must be events native to the control (such as
click, change, keydown, etc) rather than those provided by the containter
(such as Enter, BeforeUpdate, Exit, etc)

http://www.j-walk.com/ss/excel/tips/tip44.htm
Handle Multiple UserForm Buttons With One Subroutine

If you have already created individual events for each textbox, you could
have them call a common procedure to do the calculation.
 
Tom,

I was afraid that you were going to say that !

So l would have to call a procedure to sum all the tbUnit1 to tbUnit2
textboxes in each of tbUnit1x_Change events?

Seems a bit of an overkill to me but again many thanks for your help

Regards

Michael
 
I am looping through all the controls on a form and testing for "TextBox". If
true, I am adding a cell value to the textbox. However, I cannot make it loop
through the controls in the proper order. It seems to process them in the
order they were created in. changing the tab order does not help. Is there
some way that I could arrange the order that the controls are in?
 
Why not rename them to reflect the order then do

Assuming "form" means Userform

Dim tbox as MsForms.TextBox
for i = 1 to 10
tbox = userform1.Controls("Textbox" & i)
tbox.Value = Worksheets("Sheet1").Cells(i,1).Value
Next
 
I end-up having so many textboxes getting used in so many different
subroutines that I get way too confused unless I give them descriptive names.
I did find a solution, however.

Sub Test2()
Dim ob As String
For i = 2 To 7
For Each ob In UserForm1.Controls
With ob
If TypeName(Control) = "TextBox" Then
ob.Value = Cells(i, ob.TabIndex + 1)
End If
End With
Next
UserForm1.Show
Next i
End Sub

Since I set the form tab indexes to match the columns on the worksheet this
works very well.
 
Oops... sorry... ob is NOT a string...
Sub Test2()
Dim ob As Object 'or variant
For i = 2 To 7
For Each ob In frm1.Controls
With ob
If TypeName(Control) = "TextBox" Then
ob.Value = Cells(i, ob.TabIndex + 1)
End If
End With
Next
frm1.Show
Next i
End Sub
 
Back
Top