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

"Patrick" wrote:

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.

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Patrick" wrote in message
...
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?



"Tom Ogilvy" wrote:

That is one solution I suggested. The other was the John Walkenbach
approach.

--
Regards,
Tom Ogilvy

"Michael Beckinsale" wrote in message
...
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





"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy



"Michael Beckinsale" wrote in
message
...
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

"Tom Ogilvy" wrote in message
...
For i = 1 To 10
ActiveCell.Offset(i-1,0).Value = Userform1.Controls("tbUnit" &
i).Value
Next

--
Regards,
Tom Ogilvy


"Michael Beckinsale" wrote in
message
...
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