Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Happy Face gauge not updating.
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(D2650,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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Happy Face gauge not updating.
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Happy Face gauge not updating.
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. -----Original Message----- try changing to a worksheet_calculate event -- Don Guillett SalesAid Software "Mike K" wrote in message ... 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(D2650,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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Happy Face gauge not updating.
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. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Mike K wrote: 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. -----Original Message----- try changing to a worksheet_calculate event -- Don Guillett SalesAid Software "Mike K" wrote in message ... 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(D2650,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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Where can I get a free gauge chart for excel? | Charts and Charting in Excel | |||
gauge charts and dashboards | Charts and Charting in Excel | |||
Please Reply to this Thanks to all - Merry Xmas!, Happy Chanukah!, Happy ID | Excel Discussion (Misc queries) | |||
Where can I find and how to use gauge graphs? | Charts and Charting in Excel | |||
both a chart and a gauge (an activeX) | Charts and Charting in Excel |