1

I created 3 speedometers on one sheet WEstimate, but when I change information on another sheet (FCalc) I get a

Run-time error 438. Object doesn't support this property or method

I also tried using Worksheets("WEstimate") but that would keep sending me to WEstimate Sheet whenever I enter info on the FCalc Sheet which I don't want. Hope that's enough info. I have no idea what I'm doing so any help is greatly appreciated. Thank you. 

Private Sub Worksheet_Calculate()

Application.ScreenUpdating = True

Worksheets("WEstimate").Calculate

    Worksheets("WEstimate").Shapes.Range(Array("Group 2394")).Select
    Selection.ShapeRange.Rotation = ActiveSheet.Range("W199").Value * 247
    ActiveCell.Select

    Worksheets("WEstimate").Shapes.Range(Array("Group 2312")).Select
    Selection.ShapeRange.Rotation = ActiveSheet.Range("W200").Value * 247
    ActiveCell.Select

    Worksheets("WEstimate").Shapes.Range(Array("Group 2604")).Select
    Selection.ShapeRange.Rotation = ActiveSheet.Range("W202").Value * 247
    ActiveCell.Select

End Sub
GSD
  • 1,252
  • 1
  • 10
  • 12
Chuck66
  • 11
  • 1

1 Answers1

0

On the assumption that the code is behind sheet WEstimate, and some action you take on sheet FCalc causes WEstimate to recalculate, the cause of your error is that you can't Select something on a non-active sheet. And when FCalc is active, Selection referes to whatever is selected on FCalc. Classic case of why using Select Selection or Active* is a bad idea.

So, the solution is to refactor to avoid reliance on Select Selection or Active*

Something like this

Private Sub Worksheet_Calculate()
    'Application.ScreenUpdating = True '<~~ Why?
    'Worksheets("WEstimate").Calculate '<~~ This event is triggered by a recalc, so why do it again?

    Dim Speedo As ShapeRange
    Set Speedo = Me.Shapes.Range(Array("Group 2394")) '<~~ Can't select something on a non-active sheet
    Speedo.Rotation = Me.Range("W199").Value * 247 '<~~ ActiveSheet may not refer to this sheet
    ' ActiveCell.Select '<~~ not needed now

    Set Speedo = Me.Shapes.Range(Array("Group 2312")) 
    Speedo.Rotation = Me.Range("W200").Value * 247 

    Set Speedo = Me.Shapes.Range(Array("Group 2604")) 
    Speedo.Rotation = Me.Range("W202").Value * 247 

End Sub
chris neilsen
  • 52,446
  • 10
  • 84
  • 123
  • Also, [some general advise on how to avoid Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba/10717999#10717999) – chris neilsen Feb 20 '20 at 23:59
  • Thank you Chris. I had followed another example found on Youtube and tried to mimic it. I changed True to False, I kept in the recalc as I wanted it to recalc every time I changed a number on the spreadsheet and then changed the remaining programing to what you had shown and that fixed the issue and they are now working perfectly. Thanks again. – Chuck66 Feb 21 '20 at 15:54