Load Userform Control Values from Worksheet

R

RyanH

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
 
J

John Bundy

the 1 in cells(1,dblColNumber) is the row number, your offset is just
repeating a step, for example:
.tbxHeightFt = wksItem.Cells(2, dblColNumber)
.tbxHeightIns = wksItem.Cells(3, dblColNumber)
.tbxWidthFt = wksItem.Cells(4, dblColNumber)
 
R

RyanH

Yeah, you are right. The Offset is not necessary. I don't know why I did
that, duh!

Do you know of a faster way to do this code below. Like 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Load Values from Worksheet to Userform Controls with Loop 0
Load Userform Values from Sheet with Array & For...Loop 0

Top