Question on a VBA code for Speedometer Excel chart

[Smudge80]

Hello All,

Would really appreciate some assistance on this one. My speedometer dial will only rotate when I manually enter the value in cell D5 on the worksheet. I am looking for the value to be pulled from another cell in another worksheet rather than D5 on the current sheet. How can I adapt the code? When I pull the value into D5 from another sheet it wont rotate the dial.

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.Shapes.Range(Array("Group 17")).Select
Selection.ShapeRange.Rotation = Range("D5").Value * 249

ActiveCell.Select

End Sub

 

[offthelip]

put your code into the worksheet calculate event:

Private Sub Worksheet_Calculate()
ActiveSheet.Shapes.Range(Array("Group 17")).Select
Selection.ShapeRange.Rotation = Range("D5").Value * 249

ActiveCell.Select


End Sub

 

[Smudge80]

Many thanks for your reply it is much appreciated. I have a format control that adjusts value in my other charts based on the cell I am looking to refer to on a monthly basis. Can I get my format control to adjust my dial when I change the month?

 

[offthelip]

Anything you do that changes a value on the worksheet that causes it to recalculate will get it to change, so if the value in D5 changes it will adjust the dial.

 

[Smudge80]

Anything you do that changes a value on the worksheet that causes it to recalculate will get it to change, so if the value in D5 changes it will adjust the dial.

Thank you again but it does not seem to move dial when putting in =sheet2!h6 which is cell with the value in. It moves it once but then when I use the format control it stays on previous value

 

[offthelip]

I have tried using a format control on another sheet to change the value in D5 on my sheet1 and it always triggers the worksheet_calculate event. The only thing i can suggest is putting a volatile function on to the worksheet which will force a recalculation of the sheet. So try putting an offset function which references the cell that you are changing and see is that triggers the calculation