Worksheet_Change by calculated value?

[austin350s10]

I am working on a sheet that needs to run a script when specified range changes it's calculated value.

I tried using the Worksheet_Change event but the problem is that the range I am monitoring contains only calculated values not real values. When the script I have now runs it sees that the cells in the range have not changed because it is looking at the formula in the cell. Is there a way to check the calculated value instead of the formula?


Code Now:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Range1 As Range
Set Range1 = Range("A3:A10")
If Not Intersect(Target, Range1) Is Nothing Then
For Each wks In ThisWorkbook.Worksheets(Array("Blood Sugar Log", "Blood Pressure Log", "Respiration Log", "Pulse Log", "Weight Log", "Temperature Log", "Medication Log", "Blood Sugar Log", "Blood Sugar Log Plus"))
            wks.Visible = xlSheetVeryHidden
        Next wks
For Each c In Range1
Dim L1 As String
    If c <> "- Pick Log Sheet -" Then
        For Each wks In ThisWorkbook.Worksheets(Array("Blood Sugar Log", "Blood Pressure Log", "Respiration Log", "Pulse Log", "Weight Log", "Temperature Log", "Medication Log", "Blood Sugar Log", "Blood Sugar Log Plus"))
            L1 = wks.Name
                If c = L1 Then
                    wks.Visible = xlSheetVisible
                End If
        Next wks
    End If
Next c
End If
End Sub

 

[Andrew Poulsom]

What causes those cells to change, ie what formula do they contain?

 

[austin350s10]

Each cell contains a formula that refers to a cell on a different sheet. Below is one of the formulas:

='Inital Needs Assessment Final 12-2010.xlsm'!LogA

 

[Andrew Poulsom]

Shame. You will need to use the Calculate event, but you won't be able to test for Target.

 

[austin350s10]

I know it kinda sloppy...well really sloppy. I think I know how to clean in up but it doesn't seem to work for me.

I inserted the code below into the worksheet where the real values are actually changing but when it reaches the first For Each loop it doesn't seem to like the variable wks. Any ideas?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Range6 As Range
Set Range6 = Range("LogA, LogB, LogC, LogD, LogE, LogF, LogG, LogI")
If Not Intersect(Target, Range6) Is Nothing Then
For Each wks In ThisWorkbook.Worksheets(Array("Blood Sugar Log", "Blood Pressure Log", "Respiration Log", "Pulse Log", "Weight Log", "Temperature Log", "Medication Log", "Blood Sugar Log", "Blood Sugar Log Plus"))
            wks.Visible = xlSheetVeryHidden
        Next wks
For Each c In Range6
Dim L1 As String
    If c <> "- Pick Log Sheet -" Then
        For Each wks In ThisWorkbook.Worksheets(Array("Blood Sugar Log", "Blood Pressure Log", "Respiration Log", "Pulse Log", "Weight Log", "Temperature Log", "Medication Log", "Blood Sugar Log", "Blood Sugar Log Plus"))
            L1 = wks.Name
                If c = L1 Then
                    wks.Visible = xlSheetVisible
                End If
        Next wks
    End If
Next c
End If
End Sub

 

[Andrew Poulsom]

I expect that's because you are using ThisWorkbook.

 

[austin350s10]

I just tried changing it to just:

For Each [COLOR=Red]wks [/COLOR]In Worksheets(Array("Blood Sugar Log", "Blood Pressure Log", "Respiration Log", "Pulse Log", "Weight Log", "Temperature Log", "Medication Log", "Blood Sugar Log", "Blood Sugar Log Plus"))

When it gets to wks I receive the following compile error:

Variable not defined

Weird?

 

[Andrew Poulsom]

Better declare it with a Dim statement then (I expect you have Option Explicit at the top of the module).

You are going to have to qualify the worksheets with the name of the workbook. Otherwise the ActiveWorkbook will be used.

 

[austin350s10]

That was it. It got way farther in the code this time. It needs one last tweak and I think it will work fine. When it gets to the following line:

L1 = wks[COLOR=Red].Name[/COLOR]

I receive the following compile error:

Method or data member not found

Why would it do that?

Thanks for all your help. Sorry if I'm bugging you though.

 

[Andrew Poulsom]

Why would it do that?

I don't know. Can you post all the code?