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.