If I double click a cell my macro takes the target value and finds what
column the target value is in. I then scan down that column and assign all
my userform controls there values. Is there a cleaner way of doing this?

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

Dim myRef As Variant
Dim aryFormCtrls As Variant
Dim i As Long

' loads userform with stored data associated with reference number on
quote sheet
Select Case Left(Target, 2)

Case Is = "Plastic Faces"

Case Is = "Alum Faces"

Set wksItem = Sheets("Alum Faces")

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

With frmAluminumFaces
.lblRefNumber = Target
.tbxHeightFt = wksItem.Cells(1, dblColNumber).Offset(1)
.tbxHeightIns = wksItem.Cells(1, dblColNumber).Offset(2)
.tbxWidthFt = wksItem.Cells(1, dblColNumber).Offset(3)
.tbxWidthIns = wksItem.Cells(1, dblColNumber).Offset(4)
.cboFaceMaterial = wksItem.Cells(1, dblColNumber).Offset(5)
.cboMounting = wksItem.Cells(1, dblColNumber).Offset(6)
.cboFaceShape = wksItem.Cells(1, dblColNumber).Offset(7)
'paint
.chkPaint = wksItem.Cells(1, dblColNumber).Offset(8)
.chkTextured = wksItem.Cells(1, dblColNumber).Offset(9)
.tbxColorsP = wksItem.Cells(1, dblColNumber).Offset(10)
.spbColorsP = wksItem.Cells(1, dblColNumber).Offset(11)
.optSimpleP = wksItem.Cells(1, dblColNumber).Offset(12)
.optComplexP = wksItem.Cells(1, dblColNumber).Offset(13)
.cboAreaP1 = wksItem.Cells(1, dblColNumber).Offset(14)
.tbxColorP1 = wksItem.Cells(1, dblColNumber).Offset(15)
.mpgPaint.Pages(0).Visible = wksItem.Cells(1,
dblColNumber).Offset(16)
.cboAreaP2 = wksItem.Cells(1, dblColNumber).Offset(17)
.tbxColorP2 = wksItem.Cells(1, dblColNumber).Offset(18)
.mpgPaint.Pages(1).Visible = wksItem.Cells(1,
dblColNumber).Offset(19)
.cboAreaP3 = wksItem.Cells(1, dblColNumber).Offset(20)
.tbxColorP3 = wksItem.Cells(1, dblColNumber).Offset(21)
.mpgPaint.Pages(2).Visible = wksItem.Cells(1,
dblColNumber).Offset(22)
.cboAreaP4 = wksItem.Cells(1, dblColNumber).Offset(23)
.tbxColorP4 = wksItem.Cells(1, dblColNumber).Offset(24)
.mpgPaint.Pages(3).Visible = wksItem.Cells(1,
dblColNumber).Offset(25)
'vinyl
.chkVinyl = wksItem.Cells(1, dblColNumber).Offset(26)
.tbxColorsV = wksItem.Cells(1, dblColNumber).Offset(27)
.spbColorsV = wksItem.Cells(1, dblColNumber).Offset(28)
.optSimpleV = wksItem.Cells(1, dblColNumber).Offset(29)
.optComplexV = wksItem.Cells(1, dblColNumber).Offset(30)
.cboAreaV1 = wksItem.Cells(1, dblColNumber).Offset(31)
.tbxColorV1 = wksItem.Cells(1, dblColNumber).Offset(32)
.mpgVinyl.Pages(0).Visible = wksItem.Cells(1,
dblColNumber).Offset(33)
.cboAreaV2 = wksItem.Cells(1, dblColNumber).Offset(34)
.tbxColorV2 = wksItem.Cells(1, dblColNumber).Offset(35)
.mpgVinyl.Pages(1).Visible = wksItem.Cells(1,
dblColNumber).Offset(36)
.cboAreaV3 = wksItem.Cells(1, dblColNumber).Offset(37)
.tbxColorV3 = wksItem.Cells(1, dblColNumber).Offset(38)
.mpgVinyl.Pages(2).Visible = wksItem.Cells(1,
dblColNumber).Offset(39)
.cboAreaV4 = wksItem.Cells(1, dblColNumber).Offset(40)
.tbxColorV4 = wksItem.Cells(1, dblColNumber).Offset(41)
.mpgVinyl.Pages(3).Visible = wksItem.Cells(1,
dblColNumber).Offset(42)
'digital print
.chkDigitalPrint = wksItem.Cells(1, dblColNumber).Offset(43)
.cboAreaD = wksItem.Cells(1, dblColNumber).Offset(44)
'routing
.chkRouted = wksItem.Cells(1, dblColNumber).Offset(45)
.cboAreaR = wksItem.Cells(1, dblColNumber).Offset(46)
.cboBackingDeco = wksItem.Cells(1, dblColNumber).Offset(47)

.tbxCustomItem1 = wksItem.Cells(1, dblColNumber).Offset(48)
.tbxCustomItem1Cost = wksItem.Cells(1, dblColNumber).Offset(49)
.tbxCustomItem2 = wksItem.Cells(1, dblColNumber).Offset(50)
.tbxCustomItem2Cost = wksItem.Cells(1, dblColNumber).Offset(51)
.chkCrate = wksItem.Cells(1, dblColNumber).Offset(52)
.tbxCrateH = wksItem.Cells(1, dblColNumber).Offset(53)
.tbxCrateW = wksItem.Cells(1, dblColNumber).Offset(54)
.tbxCrateD = wksItem.Cells(1, dblColNumber).Offset(55)
.tbxCrateQty = wksItem.Cells(1, dblColNumber).Offset(56)
.tbxCrateCost = wksItem.Cells(1, dblColNumber).Offset(57)
.tbxQuantity = wksItem.Cells(1, dblColNumber).Offset(58)
.tbxDiscount = wksItem.Cells(1, dblColNumber).Offset(59)
.tbxComments = wksItem.Cells(1, dblColNumber).Offset(60)
End With

Call frmAluminumFaces.cmbCalculate_Click
frmAluminumFaces.Show

End Sub
--
Cheers,
Ryan