Scroll home after Autofilter select

[Guest]

I am using the following function to highlight the column header of an
activated filter.
The problem is, when someone is already far down the list when they activate
the filter, the sheet doesn’t scroll up and reveal the whole list. I tried
to incorporate an Application.GoTo routine with this, but apparently
Functions won’t allow this. Is there another way of getting around this?

Thanks,
Steve

Function FilterVal(FilterNo As Integer) As String
Dim ws As Worksheet
Set ws = Application.Caller.Parent
Application.Volatile
If ws.AutoFilter.Filters(FilterNo).On Then
FilterVal = Mid(ws.AutoFilter.Filters(FilterNo).Criteria1, 2)
Else
FilterVal = ""
End If
End Function

 

[Guest]

How are you using this function? Are you accessing it through code or are you
using it as a UDF? If you are using it as a UDF then it can not modify or
change the formatting of your sheet. It can only return a value...

 

[Guest]

Jim,

I am using it as a UDF. I suspected that I would not be able to put
anything in the function code, but was hoping for a workaround.

 

[Guest]

Nope... UDF's return values. Period. They do not change formatting or any
other settings.