dynamic hyperlink or onclick event

[Tasha]

I know how I would do the following in Access but I've been asked to do it in
Excel since it has to be distributed via email. Any help is greatly
appreciated.

I have an Access database that exports two queries to an excel spreadsheet.
In the excel spreadsheet is a main page that has a list of values. I want to
click on the value and it take me to the first query filtered by that value.
Then I want to be able to Click on a value in the filtered list and it take
me to the second query filtered by the value I just clicked on. Something
like below

Main page
ColA
1
2
3

1st query
ColA Col B
1 A
1 B
1 C
2 A
3 B
3 C

2nd Qry
ColA ColB
A yyy
A ZZZ
C XXX
C ZZZ

I want to click on 3 on the main page and it take me to the 1st query
showing only
ColA ColB
3 B
3 C

I then want to click on the C and it give take me to the 2nd query showing
only
ColA ColB
C XXX
C ZZZ

In Access I would do an OnClick event in on a form in Datasheet view but is
there anyway to do the in Excel. The report would have to be ran each week
and the data will change so a hard coded hyperlink won't work. Any
suggestions?

TIA
Tasha

[richardreye]

I would use the following code for a doubleclick event

Right-click on the 'Main Page' tab and select code. Paste the following

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

Sheets("1st Query").Select
Selection.AutoFilter Field:=1, Criteria1:=Target.Value

End Sub

Select the '1st Query' sheet in the VB Editor and paste this code

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

Sheets("2nd Query").Select
Selection.AutoFilter Field:=1, Criteria1:=Target.Value

End Sub

This assumes that either the title or data is in A1 for each query tab. All a user now needs to do is double-click on the data to be filtered by the next query. YOU may want to add some error checking but this should get you started.

HTH

[Tasha]

Perfect! TY!!


"richardreye" wrote:

>
> I would use the following code for a doubleclick event
>
> Right-click on the 'Main Page' tab and select code. Paste the
> following
>
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
> As Boolean)
>
> Sheets("1st Query").Select
> Selection.AutoFilter Field:=1, Criteria1:=Target.Value
>
> End Sub
>
> Select the '1st Query' sheet in the VB Editor and paste this code
>
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
> As Boolean)
>
> Sheets("2nd Query").Select
> Selection.AutoFilter Field:=1, Criteria1:=Target.Value
>
> End Sub
>
> This assumes that either the title or data is in A1 for each query tab.
> All a user now needs to do is double-click on the data to be filtered by
> the next query. YOU may want to add some error checking but this should
> get you started.
>
> HTH
>
>
> --
> richardreye
> ------------------------------------------------------------------------
> richardreye's Profile: http://www.excelforum.com/member.php...o&userid=14103
> View this thread: http://www.excelforum.com/showthread...hreadid=345743
>
>