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