Auto calculate active worksheet

[dgt]

Hello again

I have been trying to work out a way of updating one particular worksheet, as figures and/or formulas are changed in real time. The workbook is set to manual calculation by default.

The following code appears to work but not as quickly as I would have expected.

[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveSheet
.EnableCalculation = False
.EnableCalculation = True
.Calculate
End With
End Sub
[/vba]

So would appreciate any comments or suggestions as to changing or improving this code.

TIA ...DGT

NB: My knowledge of VBA is still very minimal.

[lucas]

I guess I don't understand fully what you are doing and the problem but why not just use:

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Me.Calculate
End Sub[/VBA]

[dgt]

Hi Lucas

Thanks for your quick response. I have changed the code to make use 0f "Me.calculate" and it appears to work in just the same way as my original code that I obtained from another example.

I have attached a tiny version of the worksheet that I am using this for and in this example, the aim is for the 'TOTAL CASH' row to update itself whenever something is entered or changed in cells B2:H15.

Of course, in the actual worksheet, the extent of the changes would be much wider and not limited to the example range of cells.

I did a quick bit of research on "Me.calculate" and nearly all of the other examples were as shown below, where the workbook is set to manual calculation.

[vba]
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
Me.Calculate

ws_exit:
Application.EnableEvents = True
End Sub

[/vba]

As I mentioned previously, my knowledge of VBA is very poor and I try to find examples of ways of solving my problems but never sure whether they need amending in any way.

Hope this helps ...DGT

PS: Forgot to mention that "Me.calculate" did not work until I had saved/closed the workbook and then re-opened it again.

[lucas]

the aim is for the 'TOTAL CASH' row to update itself whenever something is entered or changed in cells B2:H15.

DGT, In the workbook you posted, with just me.calucate in the sheet change code, I find that the row does update when I add or change numbers in the range you mention.

I can't seem to duplicate your problem.

[dgt]

Hi Lucas

I was just trying to point out that when you first save the code, it fails to work but if you close and re-open the workbook; then it appears to work fine as you can see from test workbook.

I also did a quick bit of research on "Me.calculate" and nearly all of the other examples were as shown below, where the workbook is set to manual calculation.


[vba]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Goto ws_exit: Application.EnableEvents = False
Me.Calculate
ws_exit: Application.EnableEvents = True
End Sub

[/vba]

As my knowledge of VBA is very poor and I try to find examples of ways of solving my problems, I was just trying to clarify why the other examples were a bit more complicated. Always happy to use the simpler solution.

Regards ...DGT