Use Double-Clicked cell's value to search for result on another worksheet in the same WB

Hi.
New to VBA. My knowledge is gleaned from forums just like this. Thanks to all who post solutions.

I've assembled a sheet that when one double clicks on any cell within a defined array, the cell coordinates are displayed on a msgbox (this code is placed in the Sheet code). What I need to do is use the value in the double-clicked cell to search for a match in another sheet withing the workbook. I have the other code and was working with a specified range as the search criteria, but when I try to use a variable range from the double-click code, I get an error. Here are the codes:

Double Click Code (in sheet):

Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim fCol As Long
Dim fRow As Long

If Not Intersect(Target, Range("D5:G61")) Is Nothing Then
Cancel = True
MsgBox Target.Address

End If
End Sub


Search Code (in separate Module)


Sub CopyDataToPlan()

Application.ScreenUpdating = False

Dim LCourse As String
Dim LRow As Integer
Dim LFound As Boolean

On Error GoTo Err_Execute

'Retrieve date value to search for
LCourse = Sheets("Selection Page").Target.Value <-- I tried using Target as the range from the Double-Click code, but get nothing.

Sheets("Course Detail").Select

'Start at Column A Row 3
LRow = 3
LFound = False

While LFound = False

'Encountered blank cell in row 3, terminate search
If Len(Cells(LRow, 1)) = 0 Then
MsgBox "No matching course was found."
Exit Sub

'Found match in row 3
ElseIf Cells(LRow, 1) = LCourse Then

'Select values to copy from "Course Detail" sheet
Sheets("Course Detail").Select
Cells(LRow, 6).Select
Selection.Copy

'Paste onto "Plan" sheet
Sheets("Selection Page").Select
Range("F1:G2").Select
ActiveSheet.Paste
'Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

LFound = True
End

'Continue searching
Else
LRow = LRow + 1
End If

Wend

On Error GoTo 0

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub



Any help on using the value of Target.Address from the Double-Click code in the Search code would be appreciated.

Hopefully, I've placed the code in the right way - forgive me if I haven't.

Onash.

Try:

Please Login or Register  to view this content.

Thanks for the quick response Corey. Unfortunately, I get an error with that.
I even tried to disable the double click sub and just use the "ActiveCell" range and added a search button to call on the search module and still got the error.

Onash

I believe I found the solution. I'm not sure why the syntax had to be this way, but this is how I got it to work.

Please Login or Register  to view this content.

Thanks for your help Corey.

Now I have to figure out another problem that I discovered. On to a new post.

Onash