Code sometimes works - sometimes not?!?

[gino59]

Hi all,

The following code should change the color of the shape based on the positive/negative value found in cell C11. Most times, this code works but not always and I'm stumped as to why.

Any ideas? Suggestions on cleaning it up or improving it? I'd just like it to consistently change the shape color correctly!

Many thanks as always!
Gino

'    +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'    The user selects a date from the Calendar1 control to view all transactions for that
'    date.  The table on the worksheet sums the transaction values and places the sum value
'    in cell $C$11. The value of $C$11 is entered into a text box which is grouped with the
'    "Information Shape".  If the value in cell $C$11 is negative, change the color of the
'    "Information Shape" to red.  If positive or zero, change it to green.
'    +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'
Private Sub Worksheet_Calculate()
     If Worksheets("Report").Range("$M$11").Value = "$0.00" Then
        Worksheets("Report").Shapes.Range(Array("Group 14")).Fill.ForeColor.SchemeColor = 17
     End If
     If Worksheets("Report").Range("$M$11").Value < 0 Then
         Worksheets("Report").Shapes.Range(Array("Group 14")).Fill.ForeColor.SchemeColor = 16
            Else
         Worksheets("Report").Shapes.Range(Array("Group 14")).Fill.ForeColor.SchemeColor = 17
     End If
End Sub

 

[shg]

Assuming the shape is on the worksheet where the code appears, maybe just

Private Sub Worksheet_Calculate()
    If Range("M$11").Value2 >= 0 Then
        Me.Shapes("Group 14").Fill.ForeColor.SchemeColor = 17
    Else
        Me.Shapes("Group 14").Fill.ForeColor.SchemeColor = 16
    End If
End Sub

 

[gino59]

Thanks shg! However, that still doesn't seem to fix it. It changed the color of the shape correctly the first time (went red for a negative number). Changed the date and got a positive number which showed correctly, however the shape stays red.

Seems like it works fine, if I'm running from the VBE but not on the page recalculation. Yet the value in M11 does change so I'm not sure why it works from VBE but not from the page recalc.

Cheers,
Gino

 

[CharlesChuckieCharles]

Ignore that

TRy

doevents
 
     If Worksheets("Sheet3").Range("$M$11").Value < 0 Then
         Worksheets("Sheet3").Shapes.Range(Array("Rectangle 1")).Fill.ForeColor.SchemeColor = 16
            Else
         Worksheets("Sheet3").Shapes.Range(Array("Rectangle 1")).Fill.ForeColor.SchemeColor = 17
     End If

 

[gino59]

Thanks, Charles. Even changing it to $0.00 doesn't work when the date is changed. It works when I run the code from the VBE but not when the value in M11 changes.

    If Range("$M$11").Value2 >= "$0.00" Then
        Me.Shapes("Group 14").Fill.ForeColor.SchemeColor = 17
    Else
        Me.Shapes("Group 14").Fill.ForeColor.SchemeColor = 16
    End If
End Sub

I'm guessing that even though this is on the worksheet within a Worksheet_Calculate module, it's not seeing that the value in M11 has changed due to a different date being selected.

Stumped...

 

[shg]

Are there any cells on the worksheet that depend on M11?

 

[gino59]

Hi shg! Yes, there is one cell that uses the result of cell M11. Cell S7 shows a message only if the value in cell M11 is 0 (or $0.00).

It starts with a user selecting a date from the calendar form. When that date is selected a table updates with that day's transactions. The formula in cell M11 is a sum formula of the day's transactions (+/-). So yesterday may show a positive number but last Tuesday might show a negative number. Based on the +/- of that number in M11, the shape should change color accordingly.

It's a calculation change in M11 based on the SUM formula that I'm hoping to see change the shape color. If I manually change the value in M11, the shape color change works. If I run the code from the VBE, it works. But, if I use a formula in that cell to change - it works once and then stops - even though the value changes appropriately.

I was thinking a sheet calculation should trigger the code...???

Thanks!
Gino

 

[shg]

I'm obviously missing something, because it should be triggered when M11 calculates.

What about instead using the worksheet change event for the cell(s) where the dates are entered that prompts the change?

 

[CharlesChuckieCharles]

You could try moving it to Worksheet_Change