Ctrl-Alt-Shift-F9

[Guest]

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

 

[Charles Williams]

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