Forum Discussion
ADGToreUp
Jul 25, 2022Brass Contributor
Slow Loading .xlsb File
Good morning everyone,
I have a 2.5mb .xlsb file that takes approximately five minutes to load. It is only one sheet, but has some VBA in the background. It is a tracker, and I have entered in a lot of "Worksheet_BeforeDoubleClick" functionality so that the users can easily update the tables with just using their mouse. The code below is the only code in the workbook. Everything runs very quickly once the file has opened, so I am unaware of why it takes so long to open, even from a saved copy on my desktop. (So I know that it is not our shared files where other .xlsb files open quickly) All help greatly appreciated.
VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("F11:L44")) Is Nothing Then
If Target.Value = "/" Then
Target.Value = "P"
ActiveSheet.Range("A22").Select
Exit Sub
End If
If Target.Value = "O" Then Target.Value = "/"
If Target.Value = "P" Then Target.Value = "O"
If Target.Value = Empty Then Target.Value = "/"
ActiveSheet.Range("A22").Select
End If
If Not Intersect(Target, Range("R11:T44")) Is Nothing Then
If Target.Value = "/" Then
Target.Value = "P"
ActiveSheet.Range("A22").Select
Exit Sub
End If
If Target.Value = "O" Then Target.Value = "/"
If Target.Value = "P" Then Target.Value = "O"
If Target.Value = Empty Then Target.Value = "/"
ActiveSheet.Range("A22").Select
End If
If Not Intersect(Target, ListObjects("ReviewDraftTracker").ListColumns(3).Range) Is Nothing Then
If Target.Value = "Received" Then
Target.Value = "Initializing"
ActiveSheet.Range("A22").Select
Exit Sub
End If
If Target.Value = "Routed" Then Target.Value = "Received"
If Target.Value = "Initializing" Then Target.Value = "Routed"
If Target.Value = Empty Then Target.Value = "Received"
ActiveSheet.Range("A22").Select
End If
If Not Intersect(Target, ListObjects("ReviewDraftTracker").ListColumns(12).Range) Is Nothing Then
Cancel = True
Target.Value = Date
Target.NumberFormat = "M/D/YYYY"
ActiveSheet.Range("A22").Select
End If
If Not Intersect(Target, ListObjects("ReviewDraftTracker").ListColumns(4).Range) Is Nothing Then
Cancel = True
Target.Value = Date
Target.NumberFormat = "M/D/YYYY"
ActiveSheet.Range("A22").Select
End If
If Not Intersect(Target, ListObjects("ApprovalTracker").ListColumns(1).Range) Is Nothing Then
Cancel = True
Target.Value = Date
Target.NumberFormat = "M/D/YYYY"
ActiveSheet.Range("A22").Select
End If
If Not Intersect(Target, ListObjects("ApprovalTracker").ListColumns(5).Range) Is Nothing Then
Cancel = True
Target.Value = Date
Target.NumberFormat = "M/D/YYYY"
ActiveSheet.Range("A22").Select
End If
If Not Intersect(Target, ListObjects("ReviewDraftTracker").ListColumns(2).Range) Is Nothing Then
If Target.Value = "Yes" Then
Target.Value = "No"
ActiveSheet.Range("A22").Select
Exit Sub
End If
If Target.Value = "No" Then Target.Value = "Yes"
If Target.Value = Empty Then Target.Value = "Yes"
ActiveSheet.Range("A22").Select
End If
End Sub
- You could use my free name manager addin https://jkp-ads.com/excel-name-manager.asp
- JKPieterseSilver ContributorMaybe a (hidden) range name with an external link? Those can slow down the opening considerably.
- ADGToreUpBrass ContributorWhere would I check for this? I suppose it is possible that I added something by mistake, but the code I pasted above is the only VBA used in the workbook. I checked connections and do not see any.
- JKPieterseSilver ContributorADGToreUp You can take my word for it. You should check for this as in my experience (broken) links hidden away in range names can cause precisely what you describe: a slowdown of loading your file.