View Full Version : Auto calculate active worksheet
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.
Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveSheet
.EnableCalculation = False
.EnableCalculation = True
.Calculate
End With
End Sub
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
08-01-2009, 08:45 AM
I guess I don't understand fully what you are doing and the problem but why not just use:
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Calculate
End Sub
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.
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
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
08-01-2009, 11:49 AM
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.
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.
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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.