Load Values from Worksheet to Userform Controls with Loop
 

Does anyone know of a faster way to do this code below? Maybe use a Loop
with an array or collection? I have to do code like this with about 15 other
products. So having a nice loop surely would be more pleasing. This is how
I load all the values into the Userform requested by the user.


Set wksItem = Sheets("Alum Faces")

' find column number of reference number
n = WorksheetFunction.Match(Target, wksItem.Rows("1:1"), 0)

With frmAluminumFaces
.lblRefNumber = Target
.tbxHeightFt = wksItem.Cells(2, n)
.tbxHeightIns = wksItem.Cells(3, n)
.tbxWidthFt = wksItem.Cells(4, n)
.tbxWidthIns = wksItem.Cells(5, n)
.cboFaceMaterial = wksItem.Cells(6, n)
.cboMounting = wksItem.Cells(7, n)
.cboFaceShape = wksItem.Cells(8, n)
.chkPaint = wksItem.Cells(9, n)
.chkTextured = wksItem.Cells(10, n)
.tbxColorsP = wksItem.Cells(11, n)
.spbColorsP = wksItem.Cells(12, n)
.optSimpleP = wksItem.Cells(13, n)
.optComplexP = wksItem.Cells(14, n)
.cboAreaP1 = wksItem.Cells(15, n)
.tbxColorP1 = wksItem.Cells(16, n)
.mpgPaint.Pages(0).Visible = wksItem.Cells(17, n)
.cboAreaP2 = wksItem.Cells(18, n)
.tbxColorP2 = wksItem.Cells(19, n)
.mpgPaint.Pages(1).Visible = wksItem.Cells(20, n)
.cboAreaP3 = wksItem.Cells(21, n)
.tbxColorP3 = wksItem.Cells(22, n)
.mpgPaint.Pages(2).Visible = wksItem.Cells(23, n)
.cboAreaP4 = wksItem.Cells(24, n)
.tbxColorP4 = wksItem.Cells(25, n)
.mpgPaint.Pages(3).Visible = wksItem.Cells(26, n)
.chkVinyl = wksItem.Cells(27, n)
.tbxColorsV = wksItem.Cells(28, n)
.spbColorsV = wksItem.Cells(29, n)
.optSimpleV = wksItem.Cells(30, n)
.optComplexV = wksItem.Cells(31, n)
.cboAreaV1 = wksItem.Cells(32, n)
.tbxColorV1 = wksItem.Cells(33, n)
.mpgVinyl.Pages(0).Visible = wksItem.Cells(34, n)
.cboAreaV2 = wksItem.Cells(35, n)
.tbxColorV2 = wksItem.Cells(36, n)
.mpgVinyl.Pages(1).Visible = wksItem.Cells(37, n)
.cboAreaV3 = wksItem.Cells(38, n)
.tbxColorV3 = wksItem.Cells(39, n)
.mpgVinyl.Pages(2).Visible = wksItem.Cells(40, n)
.cboAreaV4 = wksItem.Cells(41, n)
.tbxColorV4 = wksItem.Cells(42, n)
.mpgVinyl.Pages(3).Visible = wksItem.Cells(43, n)
.chkDigitalPrint = wksItem.Cells(44, n)
.cboAreaD = wksItem.Cells(45, n)
.chkRouted = wksItem.Cells(46, n)
.cboAreaR = wksItem.Cells(47, n)
.cboBackingDeco = wksItem.Cells(48, n)
.tbxCustomItem1 = wksItem.Cells(49, n)
.tbxCustomItem1Cost = wksItem.Cells(50, n)
.tbxCustomItem2 = wksItem.Cells(51, n)
.tbxCustomItem2Cost = wksItem.Cells(52, n)
.chkCrate = wksItem.Cells(53, n)
.tbxCrateH = wksItem.Cells(54, n)
.tbxCrateW = wksItem.Cells(55, n)
.tbxCrateD = wksItem.Cells(56, n)
.tbxCrateQty = wksItem.Cells(57, n)
.tbxCrateCost = wksItem.Cells(58, n)
.tbxQuantity = wksItem.Cells(59, n)
.tbxDiscount = wksItem.Cells(60, n)
.tbxComments = wksItem.Cells(61, n)
End With

Call frmAluminumFaces.cmbCalculate_Click
frmAluminumFaces.Show

Thanks in Advance!
--
Cheers,
Ryan