Worksheet VBA macro with splitt panes very slow

[spreader]

Hello all,

I am at a loss and need your crowd inteligence.

For my music repertoire I created a workbook with a songlist, that I have automated with some useful macros. Basically I can filter the songlist, open youtube videos to the song on youtube and also have put the chord sheets in the workbook, that I also can access from the central "songlist" worksheet. For convinience of stepping through the titles and show the chord charts at the same time I made use of the create new window (pane) and grouped those 2 windows vertically. That was a little bit fuzzy and trial and error until I had it function the way I wanted. The code is not so pretty - but it worked as intended.

The full file is at the moment around 600K bit and contains approximately 50 sheets with only text data. I would think that is nothing excel would get sweaty about.

As to step through the titles from the songslist I entered the code in the Worksheet_SelectionChange event so I can easily step down with the arrow keys to get the next chordsheet. This should placed right next to the songlist. Assuming there is already a second window of the same workbook open, I have this close function in the code - if nothing was open yet, the on error resume next catches that case.

For some reason the code runs in the normal usage of the automated songlist sheet incredibly slow (nearly 10 seconds on a modern laptop with I7 intel core).
When I debug the code it runs quick - now brakes, not waiting - every command seems to be executed instantly. The big question is, why the worksheet does not match the performance in exceution during Worksheet_SelectionChange event. The code I have under suspicion is yellow marked. During the worksheet event I realize, that the closing of the second pane with the chordsheets takes long. In debugging it works like a breeze (quick).

Please find the code snippet of the Worksheet_SelectionChange event procedure and the MSO version I am using.

Hope one of you can come up with an idea - I am at a loss now!

Cheers
Stefan

 

[spreader]

To show, what the frontend looks like, I attach a screenshot of the vertically alligned windows of the same workbook.

 

[Alex Blakenburg]

Can you try turning off autosave and see if it makes any difference.

 

[spreader]

Hi Alex

There is no autosave function active. See screenshot.

I am wondering if the excel installation might be corrupt.
But everything else works to my impression.

It must have something todo with the closing of sheets. In my songlist workbook I even don´t close the worksheet as a whole but only the second pane before I reopen the second pane with a different worksheet of the next song.

Cheers
Stefan

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Closing of a split pane very slow
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Alex Blakenburg]

I don't have your workbook or your environment. In my test it takes <0.5 secs.

Changing the following might be a cleaner solution but it won't make it any faster.

Swap:

    On Error Resume Next
    Windows("Songs.xlsm  -  2").Close

For:

        Dim wn As Window
        For Each wn In ThisWorkbook.Windows
            If wn.Index > 1 Then
                wn.Close
            End If
        Next wn

 

[spreader]

I found the reason for the extreme delay - although I don´t understand why it intermingles with the performance of what I do.

I have defined an alternative start-up folder in the options dialog. There are 2 addins , that I what to have started right form the begin of the excel session. THIS is causing all the trouble!

Deleting this alternative entry or also deleting the files there in fixes all performance issues here!

Why is closing a second window of a workbook such a deal and impacted be the alternative start-up folder? Amazing.....

Guess that´s it for this thread.

And thanks for the code tip Alex. I will change it.
And in fact, I don´t get the pane (window) in the vertical algintment activated as I would like. It´s always has the focus on the wrong one
If any tip is available here - I would be delighted.

Cheers
Stefan

 

[spreader]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Closing of a split pane very slow
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Acknowledged Peter. Thanks for the reminder.

 

[Alex Blakenburg]

And in fact, I don´t get the pane (window) in the vertical algintment activated as I would like. It´s always has the focus on the wrong one

Is SongList the sheet that contains the Worksheet_SelectionChange event.
Do you want that to appear on the left or the right ?
Do you want it or the other sheet to have the focus ?

 

[spreader]

Hi Alex,

yes, the songlist is the one with the selection_change event. As in the screenshot shown, the songlist is on the right side, I originally intented to have it on the left side but did not find out, how to get it over there. Basically I flip through the titles in column D and everytime I access the new title the chord sheet shall retrieve the chord-tabs worksheet of the song. The Workbook works so far, but with the allignment that the routine found of itself

The code looks like this.... Some more function you see here as to open hyperlinks when a specific text is found etc.. And in the error handling I create a new chordsheet if wished so.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo errdescr

If Target.Text = "link" Then
    Target.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End If

If Target.Column = 4 Then

 '   Target.Offset(0, 3).Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

        shtName = "__" & Mid(Target, 1, 29)

        Dim wn As Window
        Dim wnList As Window

        Set wnList = ActiveWindow

        For Each wn In ThisWorkbook.Windows
            If wn.Index > 1 Then
                wn.Close
            End If
        Next wn

        Set wn = ActiveWindow.NewWindow

        Sheets(shtName).Select

        ActiveWorkbook.Windows.Arrange ArrangeStyle:=xlVertical

        DoEvents
        Application.ScreenUpdating = True

        Call AutoScroll(wn)

End If
Exit Sub


errdescr:
  '  MsgBox err.Number & vbTab & err.Description, vbCritical + vbOKOnly, "Fehler aufgetreten!"
    If err.Number = 9 Then    'Datenblatt noch nicht vorhanden --> aussteigen
        Exit Sub
    End If
    Stop
    Resume

End Sub