I have written a custom function in Excel which works fine:

Function Sum_Dollars(Cell_Ref)
For Col_Count = 8 To 256
If Cells(5, Col_Count) = <test_val> Then Sum_Dollars = Sum_Dollars + 1
Next Col_Count
End Function

This re-calculates based on a drop down list which hides and unhides a
series of Columns in a workbook:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If OldValC1 = "" Then OldValC1 = Me.Range("C1")

If Me.Range("C1").Value <> OldValC1 Then
Cells.Select
Selection.EntireColumn.Hidden = False
If Me.Range("C1").Value = "Show All" Then
OldValC1 = Me.Range("C1").Value
Range("C1").Select
Exit Sub
End If
For Col_Count = 8 To 256
If IsEmpty(Cells(6, Col_Count)) Then Exit For

Application.EnableEvents = False
If Cells(6, Col_Count) = "$" And Cells(5, Col_Count) <>
Me.Range("C1") Then
Columns(Col_Count).Select
Selection.EntireColumn.Hidden = True
End If
Application.EnableEvents = True
Next
OldValC1 = Me.Range("C1").Value
Range("C1").Select
End If

End Sub

Problem is that my Function does not re-calculate when I change the drop
down box value. I have tried application.volatile in the function, but this
stops the "Worksheet Change" macro from working. I have tried all the
".Calculate" variations I can think of. The only thing that seems to work is
pressing "Ctrl-Alt-Shift-F9". Is there a way to program this command in VBA?
Or is there another solution to my problem?

Rich

Excel only recalculates functions when at least one of the function
arguments change: so you need to make sure that all of the cells referenced
by the function appear in the range arguments for the function.

for more information about UDFs see
http://www.DecisionModels.com/calcsecretsj.htm

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"Rich" <(E-Mail Removed)> wrote in message
news:0B4E7501-9865-4BCE-A2EA-(E-Mail Removed)...
>I have written a custom function in Excel which works fine:
>
> Function Sum_Dollars(Cell_Ref)
> For Col_Count = 8 To 256
> If Cells(5, Col_Count) = <test_val> Then Sum_Dollars = Sum_Dollars + 1
> Next Col_Count
> End Function
>
> This re-calculates based on a drop down list which hides and unhides a
> series of Columns in a workbook:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Application.ScreenUpdating = False
> If OldValC1 = "" Then OldValC1 = Me.Range("C1")
>
> If Me.Range("C1").Value <> OldValC1 Then
> Cells.Select
> Selection.EntireColumn.Hidden = False
> If Me.Range("C1").Value = "Show All" Then
> OldValC1 = Me.Range("C1").Value
> Range("C1").Select
> Exit Sub
> End If
> For Col_Count = 8 To 256
> If IsEmpty(Cells(6, Col_Count)) Then Exit For
>
> Application.EnableEvents = False
> If Cells(6, Col_Count) = "$" And Cells(5, Col_Count) <>
> Me.Range("C1") Then
> Columns(Col_Count).Select
> Selection.EntireColumn.Hidden = True
> End If
> Application.EnableEvents = True
> Next
> OldValC1 = Me.Range("C1").Value
> Range("C1").Select
> End If
>
> End Sub
>
> Problem is that my Function does not re-calculate when I change the drop
> down box value. I have tried application.volatile in the function, but
> this
> stops the "Worksheet Change" macro from working. I have tried all the
> ".Calculate" variations I can think of. The only thing that seems to work
> is
> pressing "Ctrl-Alt-Shift-F9". Is there a way to program this command in
> VBA?
> Or is there another solution to my problem?
>
> Rich
>
>