Populate Range of Cells on to Range of Userform Textboxes

  • Hi I have a spread sheet that has a grid with 31 rows and 12 columns and im trying to activate a userform which has the same amount of textboxes as there are cells in the range on the spread sheet and display the cell range into the textbox range.


    I can manually do this by using the following code,


    etc...


    But this coding is quite cumbersome (when I have all 31 rows listed) so im trying to reduce its size adapting this code,



    But I don't seem to be able to get this to work.


    Anyone point me in the right direction as to why?


    Any help is greatly appreciated.

  • Re: Populate Range of Cells on to Range of Userform Textboxes


    I can't see how it would work... You loop r from 3 to 72 yet when assigning a value to a textbox you use

    Code
    .Text = ThisWorkbook.Sheets("Master Key List").Range(IIf(c = 1, "A", IIf(c = 2, "B", IIf(c = 3, "C", "D"))) & r).Value


    Assuming c is 2 and r is 10, just for an example, substituting in those values for the IIF statement, you get

    Code
    .Text = ThisWorkbook.Sheets("Master Key List").Range("B10").Value


    But you don't reference B10 anywhere in the original Initialise event - so that's a little confusing.


    Also, the ranges are not contiguous, there are gaps: Cols A, B, D, G ... that will complicate a loop.


    You also do not give enough information to determine the sequence, can an assumption be made that the 3rd group will populate from row 145 and so on?
    And you do have 31x12 (372) textboxes on the userform?

  • Re: Populate Range of Cells on to Range of Userform Textboxes


    I thanks for the response,


    Yes if c is 2 and r is 10 then you would get cell B10.


    The columns which the information lies in are A,B,D,G,J,M,P,S,V,Y,AB,AE


    The 1st row will populate textboxes 1-12
    The 2nd row will populate textboxes 13-24
    The 3rd row will populate textboxes 25-36 etc...


    and yes there are 372 textboxes in total.


    The code im trying to adapt may not be the best choice so if you have a suggestion that may work better im happy to try it.

  • Re: Populate Range of Cells on to Range of Userform Textboxes


    Maybe something like this


    Not tested with 372 textboxes, can't imagine what that'd be like :) but the number of textboxes must match the number of cells or it will error

  • Re: Populate Range of Cells on to Range of Userform Textboxes


    Your sir are a genius!!


    Works great thank you very much for your time.


    Just for info the textboxes populate fine so that's a relief.


    Thanks again

  • Re: Populate Range of Cells on to Range of Userform Textboxes


    Minor change - while working it out I was using a loop from 1 to 31. THe final version can be modified slightly to make it a little more understandable when reading.

    Code
    For lngRowLoop = 1 To 31 
             
            For lngCtrlLoop = 0 To UBound(vCols) 
                 ' ThisWorkbook.Sheets("Master Key List").Range '// Replace into Range below
                Me.Controls("TextBox" & tbCounter).Text = Range(vCols(lngCtrlLoop) & lngRowLoop + 142).Value 
                tbCounter = tbCounter + 1 
            Next 
        Next


    Can be changed to

    Code
    For lngRowLoop = 143 To 174 '// Or whatever...
             
            For lngCtrlLoop = 0 To UBound(vCols) 
                 ' ThisWorkbook.Sheets("Master Key List").Range '// Replace into Range below
                Me.Controls("TextBox" & tbCounter).Text = Range(vCols(lngCtrlLoop) & lngRowLoop).Value 
                tbCounter = tbCounter + 1 
            Next 
        Next


    It basically removes the +142 from the line where the textbox is updated - and makes the purpose of the loop a little clearer.

Participate now!

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