Have to click formula bar and hit enter before it recalculates

Question

I have 2 worksheets.

Worksheet 1: Nicely put up data with rows like this

Row1: 5 <-- This data comes from sheet 2
Row2: 4 <-- This data comes from sheet 2
Row3: 2 <-- This data comes from sheet 2
Row4: =(Row1+Row2+Row3)/3 <-- Simple calculation from above cells.

I have this macro

Private Sub Worksheet_Change(ByVal Target As Range)

    If IsError(Cells(139, 8).Value) Then
    Call Sheet1.HideCharts
    End If

End Sub

This works perfectly, and runs when an error occured, so if i put =5/0 (which gives an error) in row 1, the HideCharts macro runs.

However. The data comes from worksheet 2, and this data comes from cubes (database) and when that data changes to i.e. nothing, it will give #REF! on worksheet 1. which i understand is an error, however then the macro won't run. What am i doing wrong? I hope you understand my question.

Edit: I found out that the macro actually semi-runs. It goes through however i have to double-click the cell and just press enter, and then it activates.

How is the data changed? Is it a formula that is being recalculated or a value being set by another macro? — RGA
I have sheet 2 where the data is imported from a database, where it is then put up nicely in sheet1, and from this data i have Row1: 5 <-- caused by these Row2: 3 <-- caused by these Row3: 4 <-- caused by these Average: =(5+3+4)/3 <-- When this changes - run macro — Mads Hjorth
Can you clarify or elaborate the line in your question ~which works if i manually change the data of the cell, however i dont manually change the data.` — skkakkar

Nathan_Sav Nathan_Sav · Accepted Answer · 2016-06-29T10:50:59

Private Sub Worksheet_Calculate()

End Sub

or you could check the changes to the vals in the formula's the way you're checking the result maybe

Have to click formula bar and hit enter before it recalculates

1 Answers