[Solved] Forms and TextBoxes

  • I have a form containing 20 tex boxes. I wish to populate these from cells in the workbook using a loop. This also leads on to saving the contents of the textboxes back to the cells when I've finished.

    I just can't seem to crack this.

    Can anyone help ??

  • I think it might help if you give a better explanation. If you are populating your textboxes from a sheet, what are you doing that will need saving ?

  • John, welcome to the Forums.
    Something like this?

    Load UserForm1
    UserForm1.TextBox1.Value = Range("a1").Value
    UserForm1.TextBox2.Value = Range("a2").Value

    ...and to save back to cells...

  • Roy,

    Basically, the form has five or so tabbed pages, each page has upwards of twenty ListBoxes. The data is inputted and I want a) to save it to the twenty or so cells (all adjacent) and b) when going into the forms at a later date, I want the ListBoxes populated with the original data so that I can review it, update it and save it as required.

    What I am trying to avoid is having over a hundred lines of code on the lines of identifying each text box and saving it to a named cell per Egad's post. (Thanks for the response though)

    I know the following doesn't work but am after something along these lines:-

    for each TextBox in UserForm1
    let sheets("Data").cells(i,1).value = TextBox.value (or text)
    next TextBox

    Also, is it possible just at the outset to use a Set command so that each cell is automatically kept up to date with each TextBox ?

    God this is fun!


  • Scouring the help files I've come up with the following code (This populates the form as opposed to saving the info):-

    Dim boxName As String
    Dim i As Integer
    Dim myControl As Control

    i = 0
    For Each myControl In Controls
    i = i + 1
    If myControl.Name Like "TextBox*" Then
    i = i + 1
    boxName = "TextBox" & CStr(i)
    Controls(boxName).Text = Sheets("Data").Cells(i + 1, 2).Value
    End If

    This works but not very well, it keeps missing boxes and then going into debug mode when i gets to 1 more than the number of textboxes on the form. I'm a lot closer but . . . .

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!