Unless your ActiveSheet happens to be either "Plastic Faces" or "Extruded
Cabinets" It won't find the value of "Target.Offset(-3)" since that was on
the ActiveSheet. What you need to do is set that value to a variable and
then use the variable in the find statement.

"RyanH" wrote:

I am getting an error on Set removeRef line. The error state Subscript out
of Range, why is this?

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

Cancel = True
On Error GoTo LineExit 'if Quote sheet is blank it ignores error
Target = ActiveCell
On Error GoTo 0

Dim myRef As Range
Dim removeRef As Range
Dim LastRow As Long
Dim FirstRow As Long
Dim 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

Select Case Left(Target.Value, 2)
Case Is = "PF"
sh = "Plastic Faces"
Case Is = "EC"
sh = "Extruded Cabinets"
End Select

'finds reference number that is above the "Remove" that was clicked
Set removeRef =
Sheets(sh).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(sh).Unprotect Password:="AdTech"

'MY CODE CONTINUES...................

Thanks,
Ryan