Programmatically add 'Input' form

Question

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.

Answer 1

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...

Answer 2

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

Answer 3

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.