Excel crashes when setting column widths of a listbox (in VBA) during a userform initialisation

Question

The Problem: My MS Excel crashes intermittently every time I try to load a userform ("MS Excel has Stopped Responding") specifically when setting column widths of a listbox.

The Background: Using a write to text file style logger I've managed to narrow the problem down to a section of code where this is happening (when it does happen) but I can't seem to see any obvious issues with my code. It seems to be happening when I am initializing a userform from a command button on a spreadsheet, more specifically when my code is setting the column widths of a number of listboxes on the userform.

The Code:

Private Sub UserForm_Initialize()

Dim strUserName As String
Dim strUserNameF As String
Dim headerARR() As Variant
Dim i As Integer

logevents (Time() & " - Loading form...")

strUserName = Environ("Username") 'for a more specific user log on number
strUserNameF = Application.UserName 'for a UI friendly log on name

'Set Labels
lblLoggedInAs.Caption = "You are currently logged in as: " & strUserNameF & " (" & strUserName & ")"
lblCurrVersion.Caption = "Current Version: " & strCurrVersion
lblLastUpdated.Caption = "Last Updated: " & strLastUpdated

logevents (Time() & " - Variables Set; Creating Tables")
Application.StatusBar = "Variables Set; Creating Tables"

logevents (Time() & " - Creating Table lbSearchTermResultsIPActions")
With lbSearchTermResultsIPActions
    .ColumnCount = 4
    .ColumnWidths = "25,50,48,150"
End With

logevents (Time() & " - Creating Table lbIPActions")
With lbIPActions
    .ColumnCount = 11
    .ColumnWidths = "40,1,28,72,70,32,53,98,60,70,70"
End With

logevents (Time() & " - Creating Table lbMyActions")
With lbMyActions
    .ColumnCount = 8
    .ColumnWidths = "44,1,47,61,127,60,50,35"
End With

logevents (Time() & " - Creating Table lbOutActions")
With lbOutActions
    .ColumnCount = 8
    .ColumnWidths = "44,1,47,61,127,60,50,35"
End With

logevents (Time() & " - Creating Table lbAllActions")
With lbAllActions
    .ColumnCount = 8
    .ColumnWidths = "44,1,47,61,127,60,50,35"
End With

logevents (Time() & " - Creating Table lbSearchTermResults")
With lbSearchTermResults
    .ColumnCount = 15
    .ColumnWidths = "25,50,50,150,100,70,70,85,50,40,65,40,40,40,40,40"
End With

logevents (Time() & " - Tables Created")
Application.StatusBar = "Tables Created"

The Output: In my log it gets to the below stage each time then crashes, though again, it does not always crash, and if I enter the VBA window then hit the button the amount of times it does happen are dramatically reduced. (No sure if this is useful info or not?)

17/11/2015 15:21:45 S***    15:21:45 - Loading form...
17/11/2015 15:21:45 S***    15:21:45 - Variables Set; Creating Tables
17/11/2015 15:21:45 S***    15:21:45 - Creating Table lbSearchTermResultsIPActions

I have tried to search here and on other forums but haven't really come across any definitive solution. I have tried putting a 1 second Application.wait after each listbox and of course tried the code without all of the write to log functions, but neither have seemed to have any effect.

Update: So I've tried to initialise the user form first; press a button on a sheet to open the user form - setting the listboxes (from design instead of code now), then have a button that processes the rest of the initialise code (setting drops downs, filling listboxes with data etc) on the user forms first tabbed page. This appears now to crash MS Excel once the second button has been pressed whilst trying to do a simple loop to populate a combo box.

Added as per Davids request:

logevents ("Starting first loop")

For i = 1 To 6
    With Controls("cbField" & i)
        .Clear
        .List = Array("", "Action_Status", "Action_Urgency", "Action_Territory", "Action_Team", "Action_Owner", "Action_Stage", "Action_Due_Date", "Attorney")
        .ListIndex = 0
    End With

Next i

Change events linked to combo boxes:

Private Sub cbField1_Change()

Select Case cbField1.Value

    Case ""
        cbOption1.Clear

    Case "Action_Urgency"
        With cbOption1
            .Clear
            .List = Array("Low", "Mid", "High")
'                .ListIndex = 0
        End With

    Case "Action_Territory"
        cbOption1.Clear
        rsARR = GetUniqueDepts
        For i = LBound(rsARR, 2) To UBound(rsARR, 2)
            cbOption1.AddItem rsARR(0, i)
        Next
        Erase rsARR

    Case "Action_Team"
        cbOption1.Clear
        rsARR = GetUniqueTeams
        For i = LBound(rsARR, 2) To UBound(rsARR, 2)
            cbOption1.AddItem rsARR(0, i)
        Next
        Erase rsARR

    Case "Action_Owner"
        cbOption1.Clear
        rsARR = GetUniqueOwners
        For i = LBound(rsARR, 2) To UBound(rsARR, 2)
            cbOption1.AddItem rsARR(0, i)
        Next
        Erase rsARR

    Case "Action_Due_Date"
        With cbOption1
            .Clear
            .List = Array("Due", "Overdue")
'                .ListIndex = 0
        End With
'            Erase rsARR

    Case "Attorney"
        cbOption1.Clear
        rsARR = GetUniqueAttorneys
        For i = LBound(rsARR, 2) To UBound(rsARR, 2)
            cbOption1.AddItem rsARR(0, i)
        Next
        Erase rsARR

    Case "Action_Status"
        cbOption1.Clear
        rsARR = GetUniqueActions_Required
        For i = LBound(rsARR, 2) To UBound(rsARR, 2)
            cbOption1.AddItem rsARR(0, i)
        Next
        Erase rsARR

    Case "Action_Stage"
        With cbOption1
            .Clear
            .List = Array("Open", "Closed")
'                .ListIndex = 0
        End With

End Select

End Sub

I really don't know what's going on here, could it be that my user form is too complex and MS Excel just can't handle all the procedures at once as I do have a few operations that run on first opening the user form?

pls try to do a .Clear of each listbox first and check if the problem still pops up — Dirk Reichel
I'm not sure it should crash, but the separator should be a semicolon rather than comma and for the last control you have 16 widths for 15 columns. Why aren't you setting these items at design time? — Rory
oh true... try to set width with: .ColumnWidths = "25 Pt;50 Pt;28 Pt;150 Pt" — Dirk Reichel
The only i can think of is that the workbook itself has an error... that means that you need to create a new workbook... however, copy worksheets or forms may also copy the error... you' d need to create just the part that are causing the error (form + macro) and test it several times... If the error doesn't pop up, then copy each part and always run tests between. Hmm... have you tried to use Application.EnableEvents = False in front of the code? (I'll delete my answer for now) — Dirk Reichel
ight now i can't think of anything except the chance there is a bug in the workbook itself... when running the code without events and having the crash at different parts of the code but only "sometimes" doesn't sound like a typo or something like that... there is still the chance of a different process which messes up the ram or something like that (but that would also cause other errors)... so there may also be the database-connection holding corrupt data.... i don't think it is an easy answer... leaves only the option of creating a new wb as a test :/ — Dirk Reichel

ppw ppw · Accepted Answer · 2015-12-09T09:50:26

Through testing numerous scenarios I discovered that whilst the workbook is open, Saving the workbook before running any code prevents any excel crashes that I was experiencing without any adjustments to my original code.

I simply added:

Private Sub Workbook_Open()

    ActiveWorkbook.Save

End Sub

To automate the process.

Note: Wanted to add this as an answer, though it is more of a workaround so I will not mark it as THE answer, but thought it may be useful to post as an answer for people who are also experiencing this problem and come across this thread.

Excel crashes when setting column widths of a listbox (in VBA) during a userform initialisation

1 Answers