Bob
Thanks for your response. Somehow, you and I skipped a beat. The macro
and the UDF are not related whatsoever. The UDF resides in D2 and counts
the visible cells. That's all it does.
The macro is triggered (Worksheet_Change) by a change in G1 (Data
Validation cell). It then sets a range, sorts that range, and clears the
Data Validation cell, G1. The UDF cell is not within the range that the
macro works with.
The problem is that if the UDF is in a cell, any cell, the macro will
not sort nor clear G1. The macro will run (MsgBox "Working") but it will
not sort nor clear G1.
Otto
"Bob Phillips" wrote in message
...
Otto,

Why would you expect it to trigger the macro? If the macro is triggered by
a
change to G1, putting that UDF in D2 should not affect it.

--

HTH

Bob Phillips

"Otto Moehrbach" wrote in message
...
Excel XP & Win XP
I have a macro that is triggered by a change in Cell G1, a Data
Validation
cell. It sets a range, sorts the range, and clears the contents of G1.
It
works just fine.

I also have a UDF as follows:
Function PupilCount() As Long
Application.Volatile
PupilCount = Range("A8", Range("A" & Rows.Count).End(xlUp)). _
SpecialCells(xlCellTypeVisible).Count
End Function

This function simple counts the visible cells in a range of Column A.
It
works just fine also.

Here is the problem. As soon as I type "=PupilCount()" (without the
quotes)
into a cell (D2) on the same sheet, the above macro no longer sorts nor
clears G1. It does nothing. No error messages. Nothing. The function
cell displays the count correctly.
What is wrong with the above function? Thanks for your help. Otto

Otto,

What's the worksheet change code look like?

--

HTH

Bob Phillips

"Otto Moehrbach" wrote in message
...
Bob
Thanks for your response. Somehow, you and I skipped a beat. The
macro
and the UDF are not related whatsoever. The UDF resides in D2 and counts
the visible cells. That's all it does.
The macro is triggered (Worksheet_Change) by a change in G1 (Data
Validation cell). It then sets a range, sorts that range, and clears the
Data Validation cell, G1. The UDF cell is not within the range that the
macro works with.
The problem is that if the UDF is in a cell, any cell, the macro will
not sort nor clear G1. The macro will run (MsgBox "Working") but it will
not sort nor clear G1.
Otto
"Bob Phillips" wrote in message
...
Otto,

Why would you expect it to trigger the macro? If the macro is triggered
by
a
change to G1, putting that UDF in D2 should not affect it.

--

HTH

Bob Phillips

"Otto Moehrbach" wrote in message
...
Excel XP & Win XP
I have a macro that is triggered by a change in Cell G1, a Data
Validation
cell. It sets a range, sorts the range, and clears the contents of
G1.
It
works just fine.

I also have a UDF as follows:
Function PupilCount() As Long
Application.Volatile
PupilCount = Range("A8", Range("A" & Rows.Count).End(xlUp)). _
SpecialCells(xlCellTypeVisible).Count
End Function

This function simple counts the visible cells in a range of Column A.
It
works just fine also.

Here is the problem. As soon as I type "=PupilCount()" (without the
quotes)
into a cell (D2) on the same sheet, the above macro no longer sorts
nor
clears G1. It does nothing. No error messages. Nothing. The
function
cell displays the count correctly.
What is wrong with the above function? Thanks for your help. Otto

Bob
Thank you for taking the time to help. The Change code is:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Address = "$L$1" Then
If Target = "" Then
Exit Sub
Else
Call SortScoresData(Target.Value)
End If
End If
End Sub

My original post said that the Target cell was G1. Now it's L1. Otto
"Bob Phillips" wrote in message
...
Otto,

What's the worksheet change code look like?

--

HTH

Bob Phillips

"Otto Moehrbach" wrote in message
...
Bob
Thanks for your response. Somehow, you and I skipped a beat. The
macro
and the UDF are not related whatsoever. The UDF resides in D2 and
counts
the visible cells. That's all it does.
The macro is triggered (Worksheet_Change) by a change in G1 (Data
Validation cell). It then sets a range, sorts that range, and clears
the
Data Validation cell, G1. The UDF cell is not within the range that the
macro works with.
The problem is that if the UDF is in a cell, any cell, the macro
will
not sort nor clear G1. The macro will run (MsgBox "Working") but it
will
not sort nor clear G1.
Otto
"Bob Phillips" wrote in message
...
Otto,

Why would you expect it to trigger the macro? If the macro is
triggered
by
a
change to G1, putting that UDF in D2 should not affect it.

--

HTH

Bob Phillips

"Otto Moehrbach" wrote in message
...
Excel XP & Win XP
I have a macro that is triggered by a change in Cell G1, a Data
Validation
cell. It sets a range, sorts the range, and clears the contents of
G1.
It
works just fine.

I also have a UDF as follows:
Function PupilCount() As Long
Application.Volatile
PupilCount = Range("A8", Range("A" & Rows.Count).End(xlUp)). _
SpecialCells(xlCellTypeVisible).Count
End Function

This function simple counts the visible cells in a range of Column
A.
It
works just fine also.

Here is the problem. As soon as I type "=PupilCount()" (without the
quotes)
into a cell (D2) on the same sheet, the above macro no longer sorts
nor
clears G1. It does nothing. No error messages. Nothing. The
function
cell displays the count correctly.
What is wrong with the above function? Thanks for your help. Otto