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