Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Populate Userform TextBox's From Cell Range (XL2000)

    I know there are better ways to do this, (Sheet Protection, Validation Rules), just humor me please. I am trying to minimize user personalities from being introduced into a sheet that needs to be mailed out daily. This sheet is generated by multiple people and each one has their own way of doing so. *sigh*

    I have a workbook containing a template and one worksheet for each of 7 units. Via a userform, the user is offered the choice of sending a sheet with default values, the previous days values adjusted to reflect the new date and user name, or the choice of modifing either the default or previous days notice. If the user should select the modify choice, a new user form is called.

    This form has a checkbox for each hour of the day running vertically along the left side of the form. There are several textboxes that correspond to each hour checkbox. If a user wishes to modify a notice, the user selects the hour checkbox, then makes the appropriate change to the desired textbox field(s). What I am trying to accomplish is to populate the textboxes from a specific range of cells when the form initializes.

    I have been playing around and can get all the textboxes to load with the first cell value or the last cell value. I cant seem to load each textbox field with one cell value from the known range. There are 192 textboxes and I am trying to stay away loading each one individually:

    txtHour1UnitLoad = Sheet("Unit 1").Range("A2").Value
    txtHour1UnitMin = Sheet("Unit 1").Range("B2").Value
    txtHour1UnitMax = Sheet("Unit 1").Range("C2").Value
    txtHour1UnitRate = Sheet("Unit 1").Range("D2").Value
    txtHour2UnitLoad = Sheet("Unit 1").Range("A3").Value
    txtHour2UnitMin = Sheet("Unit 1").Range("B3").Value
    txtHour2UnitMax = Sheet("Unit 1").Range("C3").Value
    txtHour2UnitRate = Sheet("Unit 1").Range("D3").Value

    There has got to be a way to loop through the textboxes and fill them from a range. I am so confused. Thanks in advance for any insight, assistance or good humor.
    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populate Userform TextBox's From Cell Range (XL2000)

    I haven't tested this, so it may take a little tweaking. If the textboxes are on a form named UserForm1, then something like the following should work:

    <pre>Dim I As Integer
    For I = 1 To 192
    UserForm1.Controls("txtHour" & I & "UnitLoad").Value = Worksheets("Unit 1").Range("A" & (I + 1)).Value
    UserForm1.Controls("txtHour" & I & "UnitMin").Value = Worksheets("Unit 1").Range("B" & (I + 1)).Value
    UserForm1.Controls("txtHour" & I & "UnitMax").Value = Worksheets("Unit 1").Range("C" & (I + 1)).Value
    UserForm1.Controls("txtHour" & I & "UnitRate").Value = Worksheets("Unit 1").Range("D" & (I + 1)).Value
    Next I
    </pre>

    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populate Userform TextBox's From Cell Range (XL2000)

    Once again, Thanks Legare. This board always seems to have a solution or a link to an answer.

    I played with your routine at home on a mini version and it works as advertised. Just what the doctor ordered. I was trying to meld these two loop together and was going crazy.

    <pre> Dim x
    Dim vX As Variant
    Dim rTestRange As Range
    Set rTestRange = Sheets("Sheet1").Range("A2:E4")
    For Each vX In rTestRange
    x = vX.Value
    Next vX
    </pre>

    and this
    <pre> Dim cChinga As Control
    For Each cChinga In frmTest.Controls
    If TypeName(cChinga) = "TextBox" Then
    cChinga = ""
    End If
    Next
    </pre>

    No matter how I nested these loops or assigned values, all I ended up with was all textboxes filled with the first value in the range or the last value.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •