Programmatically add 'Input' form

I'm looking to have a form generated automatically. The form would show two text boxes per account, and the account name would have to be displayed as well. The main problem is that there are a variable number of accounts to show, and that I would like a button to click to then copy the data in those text boxes to the worksheet once the user has finished.

Is there a way I can set up a 'set' of controls (my label, textbox1 and textbox2) a set number of times, then add a button at the bottom that copies the data out?

All the info I've found on the web deals with stuff way more advanced than this, and I'm sure there must be ab easier way.

From stackoverflow

• I don't know if this would work for you but the basic principle is there, I think...

  I'd suggest building a form with just the "OK" button on it with the click event of the button linked to a sub that populates your Excel sheet based on the contents of the form...

• Unless I misunderstand your question, why do you need to set up multiple controls? Why not just have a form with two text boxes and a submit button (which I think is what Jon is saying). Your code can then loop through a dynamic range (which accounts for the differing number of accounts) to populate the labels with account details as many times as is required, and then insert the data back into the workbook.

  This simple example shows what I'm on about. Set up a range in your workbook called rngTest and create a form with two textboxes, two labels and a commandbutton (default names). The data entered for each account will be entered to the right of the existing account data.

  Option Explicit
  
  Dim rngAccount As Range
  Dim lMaxAccounts As Long
  Dim x As Long
  
  Private Sub CommandButton1_Click()
      rngAccount.Offset(0, 1) = TextBox1
      rngAccount.Offset(0, 2) = TextBox2
      If Not x = lMaxAccounts Then
          x = x + 1
          Set rngAccount = Range("rngTest").Cells(x, 1)
          Label1 = rngAccount
          TextBox1 = Empty
          TextBox1.SetFocus
          TextBox2 = Empty
      Else
          Set rngAccount = Nothing
          Me.Hide
          MsgBox "All accounts completed", 0, "Done"
          Unload Me
      End If
  End Sub
  
  Private Sub UserForm_initialize()
      x = 1
      Set rngAccount = Range("rngTest").Cells(x, 1)
      lMaxAccounts = Range("rngTest").Count
      Label1 = rngAccount
  End Sub

  Chris Gunner : The nature of the data means it is a lot easier for the user to put it in if they can see the whole data as they enter it. I've actually solved this one myself, but it's hellishly complicated, and I'll post in a bit.

• Hosted here: http://www.mediafire.com/?sharekey=96919b5590638cdee5c3dee5769931ece04e75f6e8ebb871 Can anyone host it somewhere more permanent, please? I think it's a pretty neat & elegant way of doing it.