Load Userform Values from Sheet with Array & For...Loop
 

I have a Double Click Event on Sheets("QUOTE") and another sheet I use to
store data the user entered into a Userform. When the user double clicks a
cell in "QUOTE" I need the macro to scan down the data worksheet and fill in
the control values in the Userform. For some reason the Userform loads
blank, any ideas? Does anyone have any other suggestions on how to load
userform contrls from a worksheet?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

Select Case Left(Target.Value, 2)
Case Is = "TG"

' return column number of reference number in item data sheet, error
occurs if not found
dblColNumber = WorksheetFunction.Match(ActiveCell, Sheets("Tri
Graphics").Rows("1:1"), 0)

' array of controls in userform
With frmTriGraphics
aryFormCtrls = Array(.lblRefNumber, .tbxSalesNumber, .optCopyArea, _
.tbxCopyHeightFt, .tbxCopyHeightIns,
..tbxCopyWidthFt, _
.tbxCopyWidthIns, .cboLouverWidth1,
..cboOrientation1, _
.optLouverCount, .tbxLouverLengthFt,
..tbxLouverLengthIns, _
.tbxNumberOfLouvers, .cboLouverWidth2,
..cboOrientation2, _
.cboFaceType, .chkApplyGraphics, .spbGraphic1, _
.tbxGraphic1, .tbxGraphicDescription1, .spbGraphic2, _
.tbxGraphic2, .tbxGraphicDescription2, .spbGraphic3, _
.tbxGraphic3, .tbxGraphicDescription3, .spbGraphic4, _
.tbxGraphic4, .tbxGraphicDescription4, .spbGraphic5, _
.tbxGraphic5, .tbxGraphicDescription5, .spbGraphic6, _
.tbxGraphic6, .tbxGraphicDescription6, .spbGraphic7, _
.tbxGraphic7, .tbxGraphicDescription7, .spbGraphic8, _
.tbxGraphic8, .tbxGraphicDescription8, .spbGraphic9, _
.tbxGraphic9, .tbxGraphicDescription9,
..spbGraphic10, _
.tbxGraphic10, .tbxGraphicDescription10,
..tbxCustomItem1, _
.tbxCustomItem1Cost, .tbxCustomItem2,
..tbxCustomItem2Cost, _
.chkCrate, .tbxCrateH, .tbxCrateW, _
.tbxCrateD, .tbxCrateQty, .tbxCrateCost, _
.tbxQuantity, .tbxDiscount, .tbxComments)
End With

For i = LBound(aryFormCtrls) To UBound(aryFormCtrls)
aryFormCtrls(i) = Sheets("Tri Graphics").Cells(i, dblColNumber)
Next i

Call frmTriGraphics.cmbCalculate_Click
frmTriGraphics.Show

End Sub
--
Cheers,
Ryan