The UserForm is Initialized by a Command Button on my Sheets("QUOTE") or by
the BeforeDoubleClick_Event. The Error is at the Target, because all cells
are locked and protected when the Workbook is first opened, thus there are no
products added to Sheets("QUOTE") yet. The Error is a Runtime '1004' This
cell or chart you are trying to change is protected and therefore read only.
Should I just add a On Error GoTo Line 1 above the Target = ActiveCell and
then Line 1: Exit Sub.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Cancel = True
Target = ActiveCell 'unlocked cell <==ERROR ERROR
Dim myRef As Range, removeRef As Range
Dim LastRow As Long, FirstRow As Long, SelectRow As Long
'procedure if "Remove" is double clicked
If Target.Value = "Remove" Then
Response = MsgBox("Are you sure you want to remove " &
Target.Offset(-3).Text & " from the quote sheet?", vbOKCancel, "Remove
Product")
If Response <> vbOK Then Exit Sub
Application.ScreenUpdating = False
'finds reference number that is above the "Remove" that was clicked
Set removeRef = Sheets("Plastic
Faces").Rows(1).Find(What:=Target.Offset(-3).Value, _
After:=Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
'clears the particular product data that is stored in the plastic faces
worksheet
Sheets("QUOTE").Unprotect Password:="AdTech"
Sheets("Plastic Faces").Unprotect Password:="AdTech"
With Sheets("Plastic Faces").Range(removeRef.Address)
.EntireColumn.ClearContents
.Offset(0, 2).EntireColumn.ClearContents
End With
'removes data that was requested to be removed from the quote sheet
FirstRow = Range("B" & (ActiveCell.Row - 1)).End(xlUp).Row
LastRow = Range("E" & (ActiveCell.Row + 1)).End(xlDown).Row
LastRow = LastRow - 1
If LastRow = (Rows.Count - 1) Then
LastRow = Range("C" & Rows.Count).End(xlUp).Row
End If
Rows(FirstRow & ":" & LastRow).Delete
'adds thin line border around green header on quote sheet
With Range("B5,C5,D5,E5,F5")
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
'protects quote sheet then exits sub
Application.ScreenUpdating = True
Sheets("QUOTE").Protect Password:="AdTech"
Sheets("Plastic Faces").Protect Password:="AdTech"
Exit Sub
End If
'copies all values from userform to plastic face worksheet for storage
Set myRef = Sheets("Plastic Faces").Rows(1).Find(What:=Target.Value, _
After:=Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
With frmPlasticFaces
.lblRefNumber = Target.Value
.cboMaterial = Sheets("Plastic
Faces").Range(myRef.Address).Offset(1, 0).Value
.cboMoldStyle = Sheets("Plastic
Faces").Range(myRef.Address).Offset(2, 0).Value
.cboRadius = Sheets("Plastic
Faces").Range(myRef.Address).Offset(3, 0).Value
.cboMoldSeam = Sheets("Plastic
Faces").Range(myRef.Address).Offset(4, 0).Value
.chkHangRail = Sheets("Plastic
Faces").Range(myRef.Address).Offset(5, 0).Value
.tbxDimHft1 = Sheets("Plastic
Faces").Range(myRef.Address).Offset(6, 0).Value
.tbxDimHins1 = Sheets("Plastic
Faces").Range(myRef.Address).Offset(7, 0).Value
.tbxDimWft1 = Sheets("Plastic
Faces").Range(myRef.Address).Offset(8, 0).Value
.tbxDimWins1 = Sheets("Plastic
Faces").Range(myRef.Address).Offset(9, 0).Value
.tbxDimDft = Sheets("Plastic
Faces").Range(myRef.Address).Offset(10, 0).Value
.tbxDimDins = Sheets("Plastic
Faces").Range(myRef.Address).Offset(11, 0).Value
.cboFlange = Sheets("Plastic
Faces").Range(myRef.Address).Offset(12, 0).Value
.cboRetainer = Sheets("Plastic
Faces").Range(myRef.Address).Offset(13, 0).Value
.chkCopy = Sheets("Plastic
Faces").Range(myRef.Address).Offset(14, 0).Value
.optSimple = Sheets("Plastic
Faces").Range(myRef.Address).Offset(15, 0).Value
.optComplex = Sheets("Plastic
Faces").Range(myRef.Address).Offset(16, 0).Value
.chkPaint = Sheets("Plastic
Faces").Range(myRef.Address).Offset(17, 0).Value
.cboColorsP = Sheets("Plastic
Faces").Range(myRef.Address).Offset(18, 0).Value
.chk1stSurfaceP = Sheets("Plastic
Faces").Range(myRef.Address).Offset(19, 0).Value
.chk2ndSurfaceP = Sheets("Plastic
Faces").Range(myRef.Address).Offset(20, 0).Value
.cboAreaP = Sheets("Plastic
Faces").Range(myRef.Address).Offset(21, 0).Value
.chkVinyl = Sheets("Plastic
Faces").Range(myRef.Address).Offset(22, 0).Value
.cboColorsV = Sheets("Plastic
Faces").Range(myRef.Address).Offset(23, 0).Value
.chk1stSurfaceV = Sheets("Plastic
Faces").Range(myRef.Address).Offset(24, 0).Value
.chk2ndSurfaceV = Sheets("Plastic
Faces").Range(myRef.Address).Offset(25, 0).Value
.cboAreaV = Sheets("Plastic
Faces").Range(myRef.Address).Offset(26, 0).Value
.chkDigital = Sheets("Plastic
Faces").Range(myRef.Address).Offset(27, 0).Value
.chkPaintedFlange = Sheets("Plastic
Faces").Range(myRef.Address).Offset(28, 0).Value
.chk1stSurfaceD = Sheets("Plastic
Faces").Range(myRef.Address).Offset(29, 0).Value
.chk2ndSurfaceD = Sheets("Plastic
Faces").Range(myRef.Address).Offset(30, 0).Value
.cboAreaD = Sheets("Plastic
Faces").Range(myRef.Address).Offset(31, 0).Value
.chkEmbossment = Sheets("Plastic
Faces").Range(myRef.Address).Offset(32, 0).Value
.chkSingle = Sheets("Plastic
Faces").Range(myRef.Address).Offset(33, 0).Value
.cboSingle = Sheets("Plastic
Faces").Range(myRef.Address).Offset(34, 0).Value
.chkDouble = Sheets("Plastic
Faces").Range(myRef.Address).Offset(35, 0).Value
.cboDouble = Sheets("Plastic
Faces").Range(myRef.Address).Offset(36, 0).Value
.chkDebossed = Sheets("Plastic
Faces").Range(myRef.Address).Offset(37, 0).Value
.cboDebossed = Sheets("Plastic
Faces").Range(myRef.Address).Offset(38, 0).Value
.chkReader = Sheets("Plastic
Faces").Range(myRef.Address).Offset(39, 0).Value
.cboPlacard = Sheets("Plastic
Faces").Range(myRef.Address).Offset(40, 0).Value
.cboRows = Sheets("Plastic
Faces").Range(myRef.Address).Offset(41, 0).Value
.tbxReaderft = Sheets("Plastic
Faces").Range(myRef.Address).Offset(42, 0).Value
.tbxReaderins = Sheets("Plastic
Faces").Range(myRef.Address).Offset(43, 0).Value
.tbxQuantity = Sheets("Plastic
Faces").Range(myRef.Address).Offset(44, 0).Value
.tbxDiscount = Sheets("Plastic
Faces").Range(myRef.Address).Offset(45, 0).Value
.tbxCalculatedPriceEa = Sheets("Plastic
Faces").Range(myRef.Address).Offset(46, 0).Value
.tbxCalculatedPriceTotal = Sheets("Plastic
Faces").Range(myRef.Address).Offset(47, 0).Value
.tbxQuotePriceEa = Sheets("Plastic
Faces").Range(myRef.Address).Offset(48, 0).Value
.tbxQuotePriceTotal = Sheets("Plastic
Faces").Range(myRef.Address).Offset(49, 0).Value
.tbxComments = Sheets("Plastic
Faces").Range(myRef.Address).Offset(50, 0).Value
End With
Application.ScreenUpdating = True
Call frmPlasticFaces.cmbCalculate_Click
frmPlasticFaces.Show
End Sub
Private Sub UserForm_Initialize()
'everything below is what is loaded into the userform when plastic face
command button is clicked
lblRefNumber.Caption = "PF" & Str(Format(Now(), "mdyy")) & " -" &
Str(Format(Now(), "hmmss"))
With cboFlange
.AddItem "1.5"
.AddItem "2"
.AddItem "2.5"
.AddItem "3"
.AddItem "3.5"
.AddItem "4"
.AddItem "4.5"
.AddItem "5"
End With
With cboRetainer
.AddItem "1.5"
.AddItem "2.0"
End With
With cboMaterial
.AddItem "(Select One)"
.AddItem "Clear .150 High Impact Modified Acrylic"
.AddItem "Clear .150 Polycarbonate"
.AddItem "Clear .177 High Impact Modified Acrylic"
.AddItem "Clear .177 Polycarbonate"
.AddItem "White .150 High Impact Modified Acrylic"
.AddItem "White .150 Polycarbonate"
.AddItem "White .177 High Impact Modified Acrylic"
.AddItem "White .177 Polycarbonate"
End With
With cboMoldStyle
.AddItem "(Select One)"
.AddItem "Flat Face"
.AddItem "Standard Pan"
.AddItem "Custom Mold"
.AddItem "Complex Custom Mold"
.AddItem "Female Mold"
.AddItem "Free Form Female Mold"
.AddItem "Shoe Box"
End With
With cboRadius
.AddItem "No"
.AddItem "6"
.AddItem "8"
.AddItem "9"
.AddItem "10"
.AddItem "12"
End With
With cboMoldSeam
.AddItem "No"
.AddItem "1"
.AddItem "2"
.AddItem "3"
End With
For Each Control In Array(cboColorsP, cboColorsV)
With Control
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
End With
Next Control
With cboRows
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
.AddItem "6"
.AddItem "7"
.AddItem "8"
End With
For Each Control In Array(cboAreaP, cboAreaV, cboAreaD, cboSingle,
cboDouble, cboDebossed)
With Control
.AddItem "25%"
.AddItem "50%"
.AddItem "75%"
.AddItem "100%"
End With
Next Control
With cboPlacard
.AddItem "5"
.AddItem "7"
.AddItem "9"
.AddItem "10"
.AddItem "12"
End With
End Sub
Thanks RyanH