Happy Face gauge not updating.

[Mike K]

Oh wise ones,
I borrowed the HappyFace Gauge from one of
the experts website. For those unfamiliar, the face will
go from frown to smile as a cell number changes from 0-
100. The gauge works fine by manually entering a number
from 0-100. The problem arises when I use a reference to
update the cell. It just won't update. The sheet is being
refreshed every 2 seconds. What am I doing wrong?

Excel 2000
Windows 2000

Thanks, Mike

Cell contents in H3: <Updates in the cell perfectly.
=IF(D2>650,100,(D2/650)*100)

Happy face code in sheet2:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler

If Target.Address = "$H$3" Then
Application.EnableEvents = False
' min is 0.7181
' max is 0.8111
Select Case Target.Value
Case 0
Shapes("HappyFace").Adjustments.Item(1) = 0.7
Case 50
Shapes("HappyFace").Adjustments.Item(1) = 0.767
Case 100
Shapes("HappyFace").Adjustments.Item(1) = 0.9
Case Else
Shapes("HappyFace").Adjustments.Item(1) _
= 0.7181 + Target.Value / 1000
End Select
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

errHandler:
MsgBox Err.Number & " " & Err.Description
GoTo exitHandler
End Sub

 

[Don Guillett]

try changing to a worksheet_calculate event

 

[Mike K]

Don,
Can't seem to get it working. I have been doing some
reading. Mr Walkenbach does not specifically go over the
calculate event in his book. The help section is not very
helpfull. I did learn that the change event will not
recognize a calculation. I am getting compile errors.

Private Sub Worksheet_Calculate(ByVal Target As Range)

Compile error:

Proceedure declaration does not match description of event
or proceedure having the same name.

I'm guessing something in the main code is looking for a
change and not a calculation. I left it in the sheet1
area. Is the calculate event recognized as an object or
range? I would assume it is the active sheet (Sh). I'm
pretty green, but I have exhausted my printed resources

Mike.

 

[Dick Kusleika]

Mike

There's no arguments in that event. Use the dropdown boxes at the top of
the code pane to select the event, then you'll be sure to have the syntax
right. You should get

Private Sub Worksheet_Calculate()

That, of course, means you'll have to change your procedure because Target
won't be recognized anymore. You have two options: Update the chart
everytime there's a calculation whether H3 changes or not. This means
getting rid of the If that references Target and changing the Target
references elsewhere in the code to Me.Range("H3"). You might consider
naming H3 and referring to the named range in case you move that cell later.

The other option is store the value of H3 in a global variable (Dimmed in
the declarations section of a standard module, not the sheet module) and
testing the value in the calculate event so the chart only updates when that
value changes.

Personally, I'd go for the first option unless the chart takes a long time
to update (more than 1/2 second would be too long for me). If you want more
details on the second method, post back.