 # 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

And the first row is 143 and they increase by 1 each time?

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

Yep that's 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.

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

That does make more sense looking directly at the row number.

Will adapt it to the change.

Thanks again Cytop

## Participate now!

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