Slow Loading .xlsb File

[ADGToreUp]

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

[#2]

Maybe a (hidden) range name with an external link? Those can slow down the opening considerably.

[#3]

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

[#4 · Accepted solution]

You could use my free name manager addin https://jkp-ads.com/excel-name-manager.asp

[#5]

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

[#6]

I cannot use addins where i am because of the security levels. Otherwise I would try. I may send the file to my home computer and give it a try. I will let you know.

[#7]

I ended up fixing it by copying everything over to another Excel file and then adding the code in. I believe you were correct though, somewhere hidden there was a link to another document.

[#8]

I understand security levels are important, but why prevent people from using the industry-standard add-ins? Our Name Manager has hundreds of thousands of downloads and has been in use for over two decades.