Calculations are being triggered even though seyting is set to Manual

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
186
Office Version
  1. 365
Platform
  1. Windows
Hi All,

Recently I noticed my computer becoming very sluggish. Even though I have a new Acer Aspire 5 with 20GB Ram, I noticed that when I show a user form, with this code...

VBA Code:
Sub ShowInputForm()

' Show Input Form

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

frmInput.Show vbModeless

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

End Sub

This is the form:
1622432192347.png


The calculations are still being triggered.

1622431560210.png


I have noticed that excel continually wants to recalculate what I believe to be the same 8 threads. It does this many many times.

The form initialization is this:

VBA Code:
Private Sub UserForm_initialize()

    frmInput.Label3.Caption = Sheets("Build Records").Range("AA1").Value
    frmInput.Label3.ForeColor = RGB(0, 0, 255)
   
    frmInput.Label5.Caption = Sheets("Build Records").Range("AP2").Value
    frmInput.Label5.ForeColor = RGB(0, 0, 255)

    frmInput.Label7.Caption = Sheets("Build Records").Range("AR2").Value
    frmInput.Label7.ForeColor = RGB(0, 0, 255)
   
    frmInput.Label9.Caption = Sheets("Build Records").Range("AT2").Value
    frmInput.Label9.ForeColor = RGB(0, 0, 255)

End Sub

I don't use any sheet triggered events and it takes 7 seconds to show this very simple form.

I am greatly concerned that the code has been corrupted somewhere else in the model and is showing itself here :( so any guidance would be greatly appreciated

Thanks in advance

Jeff.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
186
Office Version
  1. 365
Platform
  1. Windows
Hi All, Looks like I found the issue, but I don't understand why.

In the build Records table, I replaced

=IF([Date]="","",[Date])
=VLOOKUP([Course],RaceCourse[[Course]:[Track Location]],2)
=VLOOKUP([Course],RaceCourse[[Course]:[Track Location]],3)

with

=IF(L4="","",L4)
=VLOOKUP(M4,Data!$A$3:$F$317,2,FALSE)
=VLOOKUP(M4,Data!$A$3:$F$317,3,FALSE)

Not only is everything faster once more, but my blood pressure has also recoverd.

But I don't understand why this make a difference. Has it something to do with 365?

I would love to hear some ideas.
 
Solution

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
186
Office Version
  1. 365
Platform
  1. Windows
This actually leads me to another question.

Does anybody have a simple VBA backup routine that will save the active file and add something like dat & time to distinguish file names.

I find the standard SAVE AS routine rather cumbersome because doing a SAVE AS changes the active file to the recently saved backup file and that causes havoc with my custom tool bars.

Thanks
 

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
186
Office Version
  1. 365
Platform
  1. Windows
Found one on Dr Googe that does a good enough job.
 

Forum statistics

Threads
1,141,046
Messages
5,703,925
Members
421,321
Latest member
blusky4

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top