Populate Range of Cells on to Range of Userform Textboxes

GhostMonkey72 · Jun 3rd 2016

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,

Code

Private Sub UserForm_Initialize()
'1st
TextBox1.Value = Range("A143")
TextBox2.Value = Range("B143")
TextBox3.Value = Range("D143")
TextBox4.Value = Range("G143")
TextBox5.Value = Range("J143")
TextBox6.Value = Range("M143")
TextBox7.Value = Range("P143")
TextBox8.Value = Range("S143")
TextBox9.Value = Range("V143")
TextBox10.Value = Range("Y143")
TextBox11.Value = Range("AB143")
TextBox12.Value = Range("AE143")
'2nd
TextBox13.Value = Range("A144")
TextBox14.Value = Range("B144")
TextBox15.Value = Range("D144")
TextBox16.Value = Range("G144")
TextBox17.Value = Range("J144")
TextBox18.Value = Range("M144")
TextBox19.Value = Range("P144")
TextBox20.Value = Range("S144")
TextBox21.Value = Range("V144")
TextBox22.Value = Range("Y144")
TextBox23.Value = Range("AB144")
TextBox24.Value = Range("AE144")
'3rd

Display More

etc...

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

Code

Private Sub UserForm_Initialize()
Dim ctrlNum As Integer
 
ctrlNum = 1
     
For r = 3 To 72
For c = 1 To 4
    With KeyList.Controls("TextBox" & ctrlNum)
                 
        .Text = ThisWorkbook.Sheets("Master Key List").Range(IIf(c = 1, "A", IIf(c = 2, "B", IIf(c = 3, "C", "D"))) & r).Value
                
        .BackColor = (ThisWorkbook.Sheets("Master Key List").Range("A" & r).Interior.Color)
        .ForeColor = (ThisWorkbook.Sheets("Master Key List").Range("A" & r).Font.Color)
    End With
             
    ctrlNum = ctrlNum + 1
    Next
Next
End Sub

Display More

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.

cytop · Jun 3rd 2016

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?

GhostMonkey72 · Jun 3rd 2016

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.

cytop · Jun 3rd 2016

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

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

GhostMonkey72 · Jun 3rd 2016

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

Yep that's it

cytop · Jun 3rd 2016

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

Maybe something like this

Code

Private Sub UserForm_Initialize()


   Dim vCols As Variant
   Dim lngRowLoop As Long
   Dim lngCtrlLoop As Long
   Dim tbCounter As Long
   
   tbCounter = 1
   vCols = Array("A", "B", "D", "G", "J", "M", "P", "S", "V", "Y", "AB", "AE")
   
   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
         
End Sub

Display More

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

GhostMonkey72 · Jun 3rd 2016

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

cytop · Jun 3rd 2016

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.

GhostMonkey72 · Jun 3rd 2016

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

Populate Range of Cells on to Range of Userform Textboxes

Participate now!

Share