Hyperlinks and Automatic Filtering

[Bob P]

I've built a hyperlink from one worksheet to another which works fine, but I
want to automatically filter the data in the destination worksheet - can
this be done?

Thanks,
Bob.

 

[Dave Peterson]

There is a worksheet_activate event that you might be able to use to.

It works in xl2002 when you go to a worksheet via hyperlink, but I recall an
earlier discussion that said it didn't fire in either xl97 or xl2k. (bad
memory!)

Try right clicking on the worksheet tab that you jump to.
Select view code and paste this in.

Option Explicit
Private Sub Worksheet_Activate()
MsgBox "hi from " & Me.Name
End Sub

Now back to excel and back to that other worksheet. Click on the link.

Did you get a message? If yes, then you could add some code in this event to
filter that worksheet.

If no, maybe you could replace the link with a macro that's assigned to a nice
button from the Forms toolbar:

Option Explicit
Sub fakelink()
Application.Goto Worksheets("Sheet1").Range("a1")
End Sub


I think that the worksheet_activate will fire in all versions (I know it does in
xl2002).

Record a macro when you filter that sheet the way you want. Plop it into the
worksheet_activate routine and see if it works. (Post back if you need help
with your macro--I'm sure you'll get some help.)

 

[Bob P]

Dave that worked OK - Thanks.

However I am COMPLETELY new to VB and am having trouble writing the code.
When I click the hyperlink, I need the code to search in range C13:C33, and
only display rows where the text matches "critical" on the destination
worksheet. Can anyone provide some sample code for this?

Many Thanks,
Bob.

 

[Dave Peterson]

In your worksheet_activate code, you could do something like:

Option Explicit
Private Sub Worksheet_Activate()

Dim myRng As Range
Set myRng = me.Range("C12:c33")
me.AutoFilterMode = False

myRng.AutoFilter field:=1, Criteria1:="critical"

End Sub

I removed any existing autofilter and then applied the filter (one row up for
the header).

If the existing autofilter is A12:x9999 (includes your column), you could:

Option Explicit
Private Sub Worksheet_Activate()

Dim myRng As Range

With Me
Set myRng = .Range("C12:C33")
If Intersect(myRng, .AutoFilter.Range) Is Nothing Then
'msgbox "filter in the wrong spot"
Exit Sub
Else
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
.AutoFilter.Range.AutoFilter field:=3, Criteria1:="critical"
Else
'do nothing
MsgBox "worksheet must have autofilter"
End If
End If
End With

End Sub

 

[Bob P]

Dave - You are the man!! Works a treat.

Many Thanks,
Bob