Thanks for your responses. I am getting a Type Mismatch Error. Forgive me I
am new to VBA. I am still trying to learn the errors, because I get a lot of
them, lol.

My goal here is to double click on a Reference Number (Target) and my code
should extract the first to letters of the reference number. These first two
letters will tell the code what worksheet the reference number will be found.
At that point it will edit data or clear data. Here is the first portion of
my code, which still contains the error.

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

'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:=Sheets(sh).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"

With Sheets(sh).Range(removeRef.Address)
.EntireColumn.ClearContents
.Offset(0, 2).EntireColumn.ClearContents
End With

'code continues from here........

Thanks again Ryan

"Dave Peterson" wrote:

I'd change this portion:
After:=Cells(1), _
to
After:=Sheets(sh).Cells(1), _

Unqualified ranges in the worksheet module will refer to the sheet owning the
code--probably not what you wanted.

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

--

Dave Peterson