Application.Volatile messing up other functions

[itchyII]

Hi Everyone,
I have an Excel(2000) workbook with a few different sheets which have code behind them as well as some additional functions in a module. It is a workbook to track survey responses. In the first sheet "Survey Tracking", I track who has returned a survey and who has not. In the next sheet "Responses" I track the responses to the questions that each person made. In the Third sheet "Survey Stats" I calculate some statistics based on the "Survey Tracking" sheet (stuff like how many people responded to the survey etc.). There is a field in the “Survey Tracking” sheet with a drop down list containing “Yes, No, Declined and Cancelled”. If the user makes a selection in this box, the sheet fires some functions that format the cells in that row based on the input and then prompts the user asking if they want to go to the “Responses” sheet to enter the responses now (if they click yes, they are brought to the corresponding row in the “Responses” sheet. Now, once the field in the “Survey Tracking” sheet changes, it will affect some functions in the “Survey Stats” sheet. In that sheet, I have cells referencing functions in my module that make calculations based on the “Survey Tracking” sheet. The problem is that in order for the functions in the “Survey Stats” sheet to be recalculated every time there are changes in the “Survey Tracking” sheet, I have declared them as volatile. However, in doing so, my functions in the “Survey Tracking” sheet no longer work! If I comment out all of the ‘Application.Volatile’ lines in my functions, the functions in the “Survey Tracking” sheet work fine! Why is this happening and how can I fix it?

Here is the code in “Survey Tracking”

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Call formatHighlight(Target.row)
Call formatFont(Target.row)
With ActiveCell
If .Column = 13 Then
If .Value = "Yes" Then
If MsgBox("Do you wish to enter this client's survey responses now?", vbYesNo, "Enter Survey Responses") = vbYes Then
Worksheets("Responses").Activate
ActiveSheet.Range("B" & Target.row).Select
End If
End If
End If
End With

End Sub

Function formatHighlight(row As Integer)

With ActiveSheet.Range("O" & row)
If .Value = "Yes" Then
ActiveSheet.Range("B" & row & ":" & "P" & row).Interior.ColorIndex = 4
ActiveWorkbook.Worksheets("Responses").Range("A" & row & ":" & "Y" & row).Interior.ColorIndex = 4
Else
With ActiveSheet.Range("M" & row)
If .Value = "Yes" Then
ActiveSheet.Range("B" & row & ":" & "P" & row).Interior.ColorIndex = xlColorIndexNone
ActiveWorkbook.Worksheets("Responses").Range("A" & row & ":" & "Y" & row).Interior.ColorIndex = xlColorIndexNone
ElseIf .Value = "No" Then
ActiveSheet.Range("B" & row & ":" & "P" & row).Interior.ColorIndex = 6
ActiveWorkbook.Worksheets("Responses").Range("A" & row & ":" & "Y" & row).Interior.ColorIndex = 6
ElseIf .Value = "Declined" Then
ActiveSheet.Range("B" & row & ":" & "P" & row).Interior.ColorIndex = xlColorIndexNone
ActiveWorkbook.Worksheets("Responses").Range("A" & row & ":" & "Y" & row).Interior.ColorIndex = xlColorIndexNone
ElseIf .Value = "Cancelled" Then
ActiveSheet.Range("B" & row & ":" & "P" & row).Interior.ColorIndex = xlColorIndexNone
ActiveWorkbook.Worksheets("Responses").Range("A" & row & ":" & "Y" & row).Interior.ColorIndex = xlColorIndexNone
End If
End With
End If
End With

End Function

Function formatFont(row As Integer)

With ActiveSheet.Range("M" & row)
If .Value = "Yes" Then
ActiveSheet.Range("B" & row & ":" & "N" & row).Font.Strikethrough = False
ActiveWorkbook.Worksheets("Responses").Range("A" & row & ":" & "Y" & row).Font.Strikethrough = False
ElseIf .Value = "No" Then
ActiveSheet.Range("B" & row & ":" & "N" & row).Font.Strikethrough = False
ActiveWorkbook.Worksheets("Responses").Range("A" & row & ":" & "Y" & row).Font.Strikethrough = False
ElseIf .Value = "Declined" Then
ActiveSheet.Range("B" & row & ":" & "N" & row).Font.Strikethrough = True
ActiveWorkbook.Worksheets("Responses").Range("A" & row & ":" & "Y" & row).Font.Strikethrough = True
ElseIf .Value = "Cancelled" Then
ActiveSheet.Range("B" & row & ":" & "N" & row).Font.Strikethrough = True
ActiveWorkbook.Worksheets("Responses").Range("A" & row & ":" & "Y" & row).Font.Strikethrough = True
End If
End With

End Function


Here is the code in my module that is referenced in “Survey Stats”

Option Explicit

Public Function findNumRows(wks As Worksheet, col As String, headers As Integer) As Integer
Dim row As Integer

'set starting row
row = headers
While wks.Range(col & row + 1).Value <> "" 'loop until no data in row
row = row + 1
Wend
row = row - headers 'remove number of rows for headers

findNumRows = row

End Function

Function countSent(which As String) As Integer

'Application.volatile

Dim numRows As Integer
Dim x As Integer
Dim count As Integer

numRows = findNumRows(Worksheets("Survey Tracking"), "F", 2)
count = 0
If which = "Total" Then
For x = 1 To numRows
If Not ((Worksheets("Survey Tracking").Range("J" & (x + 2)) = "" Or IsNull(Worksheets("Survey Tracking").Range("J" & (x + 2)))) _
And (Worksheets("Survey Tracking").Range("K" & (x + 2)) = "" Or IsNull(Worksheets("Survey Tracking").Range("K" & (x + 2))))) Then
count = count + 1
End If
Next x
Else
For x = 1 To numRows
If Worksheets("Survey Tracking").Range("D" & (x + 2)) = which _
And (Not (IsNull(Worksheets("Survey Tracking").Range("J" & (x + 2))) _
Or IsNull(Worksheets("Survey Tracking").Range("K" & (x + 2))))) Then
count = count + 1
End If
Next x
End If

countSent = count

End Function


Function countRCVD(which As String) As Integer

'Application.volatile

Dim numRows As Integer
Dim x As Integer
Dim count As Integer

numRows = findNumRows(Worksheets("Survey Tracking"), "F", 2)
count = 0

If which = "Total" Then
For x = 1 To numRows
If Worksheets("Survey Tracking").Range("M" & (x + 2)) = "Yes" Then
count = count + 1
End If
Next x
Else
For x = 1 To numRows
If Worksheets("Survey Tracking").Range("D" & x) = which _
And Worksheets("Survey Tracking").Range("M" & x) = "Yes" Then
count = count + 1
End If
Next x
End If

countRCVD = count

End Function


Sorry for being so long winded!!!



__________________
ItchyII

I will not be defeated!

 

[SkipVought]

Hi,

Try moving the Application.Volitile to BEFORE the function assign statement -- for instance in the FIRST function...

....

Application.Volitile

countSent = count

End Function

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[itchyII]

Hi Skip, thanks for the response. I tried it but it made no difference. Any other ideas?

ItchyII

 

[SkipVought]

It was a shot in the dark.

I have no other thoughts.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884