filter using double clicked cell content

Microsoft Microsoft excel 2007 open lice...
June 30, 2010 at 23:13:57
Specs: Windows XP
I am doing a"pro bono" project for my church to manage a new area of the churchyard (place where cremated remains can be buried) I am not a VBA programmer but an "able amateur". So far we are able to double click on any cell (any one of 150) in an excel worksheet and run a macro to open the access database and open a form. I want to be able to filter the form with the content of the cell that was double clicked. The code I have found on the internet will do what I require except it will only filter with a specified cell address (A2). How can I remove A2 and replace it with the address of the cell that was double clicked or use a new line of code that will use the content of the double clicked cell as a filter

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

Dim LPath As String
Dim strLocation As String

'Path to Access database
LPath = "C:\Documents and Settings\Stuart\My Documents\Database1.accdb"

'Open Access and make visible
Set oApp = CreateObject("Access.Application")
oApp.Visible = True

'Open Access database as defined by LPath variable
oApp.OpenCurrentDatabase LPath

'Open form called Input filtering by Location
strLocation = Range("A2").Value
oApp.DoCmd.OpenForm "Input", , , "Location = '" & strLocation & "'"

End Sub

Many thanks for your attention



See More: filter using double clicked cell content

July 1, 2010 at 05:04:10
Try changing:

strLocation = Range("A2").Value

strLocation = Target.Value

In an Event macro, Target is used to identify the cell that triggered the event.

You can use things like these (and others) to control how your macro uses the Target cell


Report •

July 1, 2010 at 10:14:59
Many thanks, I may need more help, is it OK to contact you again?



Report •

July 1, 2010 at 10:21:38
If you have any more questions regarding this specific question (the DoubleClick macro) then feel free to continue replying in this thread.

If you have other questions on other issues (even if it's for the same project), please start a new thread with a relevant subject line.

By keeping unrelated topics separate, and identified with a relevant subject line, the archives stay neater and threads are easier to find and follow.

Report •
Related Solutions

Ask Question