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 doesnt scroll up and reveal the whole list. I tried
to incorporate an Application.GoTo routine with this, but apparently
Functions wont 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

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...
--
HTH...

Jim Thomlinson


"Steve" wrote:

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 doesnt scroll up and reveal the whole list. I tried
to incorporate an Application.GoTo routine with this, but apparently
Functions wont 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

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.

"Jim Thomlinson" wrote:

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...
--
HTH...

Jim Thomlinson


"Steve" wrote:

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 doesnt scroll up and reveal the whole list. I tried
to incorporate an Application.GoTo routine with this, but apparently
Functions wont 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

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

Jim Thomlinson


"Steve" wrote:

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.

"Jim Thomlinson" wrote:

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...
--
HTH...

Jim Thomlinson


"Steve" wrote:

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 doesnt scroll up and reveal the whole list. I tried
to incorporate an Application.GoTo routine with this, but apparently
Functions wont 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