Macro to display ERROR if sheet not calculated

[Simmo1313]

Hi all,

Looking for what I assume would be a relatively simple macro but I am very new to VBA so don't know how to do it myself.

I have a large spreadsheet that is too bulky to run on auto calculate formulas so we leave it in manual.

I have made a push button that calculates the page but was hoping there was a way I could display in a cell somewhere anytime a change to the inputs of the sheet are made and calculate is not the last function the spreadsheet has completed.

There are 20+ sheets on the page but I would only need the display visible on our "INPUTS1" page.

any help would be greatly appreaciated

 

[mrmmickle1]

Where are your inputs at (What Cells or ranges)?

You can probably use a worksheet change event to determine when one off these cells has been changed....

When the Worksheet change event fires you could put in a specific cell what cell was changed as well as a timestamp

You could also add a timestamp to the calculate button so you would know the last time the sheet was calculated...

 

[Simmo1313]

Thanks you for replying

There are inputs on a lot of different pages so it might be hard with that approach.

I was hoping there might be a way of tracking what the last function performed was and if it wasn't "calculate" then display text in a cell.

If that's not possible then adding a time stamp to the calculate function would probably be the next best solution. How would I go about that?

Much appreciated

 

[mrmmickle1]

Calculate is a Method of the Worksheet object. It's not a function. (Calculate is a DAX function which relates to PowerPivot , but based on your description I don't think this is what your referring too)

There is a property of the Application Object called Caller which will tell you from what button a sub procedure is called. Would this be any help?

Another Idea is to have a change log sheet that logs all changes that are made....before value...after value...timestamp of when the change occurred.

Are you using code similar to this?:

[COLOR=#0000ff]Sub[/COLOR][COLOR=#000000] CalcBook()[/COLOR]    
[COLOR=#0000ff]    Dim[/COLOR] wks [COLOR=#0000ff]As [/COLOR]Worksheet
    Application.Calculation = xlManual
  [COLOR=#0000ff]  For Each[/COLOR] wks [COLOR=#0000ff]In[/COLOR] ActiveWorkbook.Worksheets
        wks.Calculate
    [COLOR=#0000ff]Next[/COLOR]
   [COLOR=#0000ff] Set[/COLOR] wks =[COLOR=#0000ff] Nothing[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]