Double Click Event Error on Worksheet

R

RyanH

I have a Worksheet that my users use to quote particular jobs. The entire
worksheet is locked and protected. The only way they can add/edit data is
via a UserForm. When they add a particular product, a cell labeled "Edit" is
unlocked. I have a Before Double Click Event that the user uses to double
click that cell and the UserForm shows up to make edits.

My issue is when there are no products on the worksheet. The entire sheet
is locked and protected. When I double click something an error shows up. I
don't want this to confuse the User. Is there a way to ignore that
particular error, but show other errors for debugging? Or have a MsgBox show
up saying something?

Thanks,
Ryan
 
N

Nigel

Without posting your code it is pretty difficult to diagnose. Sounds like
the UserForm initialize event is causing the error? Post both codes sets.
 
R

RyanH

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
 

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

How disable an Excel dialog box? 4
Keep Cell Unlocked but Don't Allow User to Change the Text in Cell 8
"Double Click" macro? 3
Userform textbox string to header causes unwanted double spacing 4
BeforeDoubleClick on Protected Sheet 2
Problem disabling double click event 1
Select Cell within range 3
Change a default Excel warning 1

Top