Calculations are being triggered even though seyting is set to Manual

[JeffGrant]

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...

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:

The calculations are still being triggered.

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:

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.

 

[JeffGrant]

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.

 

[JeffGrant]

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]

Found one on Dr Googe that does a good enough job.