Solved

Excel VBA error:  Microsoft Excel Has Stopped Working

Posted on 2010-12-05
4,049 Views
Last Modified: 2012-05-10
I am using Microsoft Excel 2010 and have a rather complex user form that has 6 rows of 30 controls each on one tab of a MultiPage control.  There are several other Tabs/Pages in the form, but this particular Tab/Page oftentimes throws the error "Microsoft Excel has stopped working."  This problem does not occur every time the Form is used, but it does occur frequently enough to be frustrating.  

I have perused the Microsoft KB article 319832, "INFO: Error or Unexpected Behavior with Office Automation When You Use Early Binding in Visual Basic" but I still get the message even after ensuring that Option Explicit is in place.  I have also set VBA to "Break on all errors" but this error does not cause any offending line to be highlighted.

Once the issue does occur, exporting then deleting then importing the user form seems to correct the problem -- for a while, but not permanently.

Any ideas on what might be causing it or what steps I can take to find the offending code?  
0
Question by:malcolm29
    19 Comments
     
    LVL 3

    Expert Comment

    by:SteveZ
    What operating system are you using?

    Can you post the offending code?

    There are many reasons why you'll get that message, is there additional information?  Are you getting an APPCRASH or another type of error?  Are you getting prompted to Wait for the Application to Respond?
    0
     

    Author Comment

    by:malcolm29
    Thanks for your help.

    I am using Windows 7 Professional, 64-bit.  I get no other error message than "Microsoft Excel has stopped working."  It then says "windows is checking for a solution" and then eventually Excel closes with no further messages.  Thanks for asking about the APPCRASH info.  It prompted me to look In the Error Log/Application, where there is the following message:

    Faulting application name: EXCEL.EXE, version: 14.0.4756.1000, time stamp: 0x4b9c08e8
    Faulting module name: VBE7.DLL, version: 7.0.15.90, time stamp: 0x4b7b1248
    Exception code: 0xc0000005
    Fault offset: 0x000049ac
    Faulting process id: 0x1230
    Faulting application start time: 0x01cb949bcb59ca96
    Faulting application path: C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE
    Faulting module path: C:\PROGRA~2\COMMON~1\MICROS~1\VBA\VBA7\VBE7.DLL
    Report Id: 0cb033a3-008f-11e0-8d7f-0021869e9031

    In addition, the faulting code is probably too big to include, but I'll include 2 procedures.  The first is what gets called when someone selects a tab on the MultiPage control (or any where on the MultiPage where there isn't another control).  The second is the code that initializes the controls on tab causing the error (Case 2).

    Private Sub MultiPage1_Click(ByVal Index As Long)
        If OnPage <> MultiPage1.Value Then ' the variable OnPage is used to avoid resetting a page if someone clicks on a part of the page that doesn't have a control
            If AddedPerson <> 0 Then
                MultiPage1.Value = OnPage
                MsgBox ("You must select Cancel if you want to leave this tab without confirming your action.")
            Else
                OnPage = MultiPage1.Value
                Select Case MultiPage1.Value
                Case 0
                    Call schUserForm_Activate
                Case 1
                    Call cantUserForm_Activate
                Case 2
                    Call minUserForm_Activate
                Case 3
                    Call posUserForm_Activate
                Case 4
                    Call massUserForm_Activate
                Case 5
                    Call cfgUserForm_Activate
                Case Else
                    ' put help page info here
                End Select
            End If
        End If
    End Sub
    

    Open in new window


    Private Sub minUserForm_Activate()
        Dim i, j, x, NumMinistries, NumMasses, LenOfText, WidOfBox, StartRow, StartCol, NumPositions, LeftPos, CenterTop As Integer
        AddedPerson = 0
        minOKButton.Caption = "OK and Done"
        minOKAndEditButton.Caption = "OK and Edit Another"
        minCBSelectMinister.Value = ""
        ' disable all controls until name is selected
        ' disable, hide, and set value of ministry grid items
        If minToggleButton.Value = False Then
            minLblClick1.Visible = False
            minLblClick2.Visible = False
            minLblClick3.Visible = False
            minLblClick4.Visible = False
            minLblClick5.Visible = False
            minLblPercAvail.Visible = False
        Else
            minToggleButton.Caption = "Advanced View"
            minLblClick1.Visible = True
            minLblClick2.Visible = True
            minLblClick3.Visible = True
            minLblClick4.Visible = True
            minLblClick5.Visible = True
            minLblPercAvail.Visible = True
        End If
        For i = 1 To MaxNumMinistries
            ' disable, hide, and set value of ministry name grid items
            Controls("LabelMinistry" & CStr(i)).Object.Enabled = False
            Controls("LabelMinistry" & CStr(i)).Visible = False
            Controls("LabelMinistry" & CStr(i)).Object.Caption = ""
        Next i
        For j = 1 To MaxNumMasses
            ' disable, hide, and set value of mass name grid items
            Controls("LabelMass" & CStr(j)).Object.Enabled = False
            Controls("LabelMass" & CStr(j)).Visible = False
            Controls("LabelMass" & CStr(j)).Object.Caption = ""
        Next j
        For i = 1 To MaxNumMinistries
            For j = 1 To MaxNumMasses
                ' disable, hide, and set value of ministry serve grid items
                Controls("TB" & CStr(i) & CStr(j)).Object.Enabled = False
                Controls("TB" & CStr(i) & CStr(j)).Visible = False
                Controls("TB" & CStr(i) & CStr(j)).Object.Value = ""
                Controls("CheckBox" & CStr(i) & CStr(j)).Object.Enabled = False
                Controls("CheckBox" & CStr(i) & CStr(j)).Visible = False
                Controls("CheckBox" & CStr(i) & CStr(j)).Object.Value = False
            Next j
            For j = 1 To MaxNumPositions
                ' disable, hide, and set value of positions serve grid items
                Controls("Lbl" & CStr(i) & CStr(j)).Object.Enabled = False
                Controls("Lbl" & CStr(i) & CStr(j)).Visible = False
                Controls("Lbl" & CStr(i) & CStr(j)).Object.Caption = ""
                Controls("Lbl" & CStr(i) & CStr(j)).Object.SpecialEffect = fmSpecialEffectSunken
            Next j
        Next i
        ' ==================================
        ' Configure active ministry, mass, and position values
        ' ==================================
        ' set value of ministry grid items
        ' ==================================
        Sheets("Setup Sheet").Select
        NumMinistries = Range("NumberOfMinistries").Value
        NumMasses = Range("NumberOfMasses").Value
        For i = 1 To MaxNumMinistries
            If i <= NumMinistries Then
                ' set value of ministry name grid items
                ' enable text
                Controls("LabelMinistry" & CStr(i)).Visible = True
                Controls("LabelMinistry" & CStr(i)).Object.Caption = Cells(MinistryNamesRangeRow + i, MinistryNamesRangeCol).Value
                ' vertically center text
                LenOfText = Controls("LabelMinistry" & CStr(i)).Object.Caption
                LenOfText = (Len(LenOfText) * Controls("LabelMinistry" & CStr(i)).Object.FontSize) / 2
                WidOfBox = Controls("LabelMinistry" & CStr(i)).Width
                If LenOfText >= WidOfBox Then
                    Controls("LabelMinistry" & CStr(i)).Top = Controls("TB" & CStr(i) & "1").Top - 2
                End If
            End If
        Next i
        For j = 1 To MaxNumMasses
            If j <= NumMasses Then
                ' set value of mass name grid items
                Controls("LabelMass" & CStr(j)).Visible = True
                Controls("LabelMass" & CStr(j)).Object.Caption = Cells(MassTimesRow + j - 1, MassTimesCol).Value
            End If
        Next j
        For i = 1 To NumMinistries
            ' get row and col for range for each ministry type
            StartRow = Range("Ministry" & CStr(i) & "MassPositionsRange").Row
            StartCol = Range("Ministry" & CStr(i) & "MassPositionsRange").Column
            For j = 1 To NumMasses
                ' need to put check boxes in proper location on form (TBXX are already in proper location)
                Controls("CheckBox" & CStr(i) & CStr(j)).Top = Controls("TB" & CStr(i) & CStr(j)).Top
                Controls("CheckBox" & CStr(i) & CStr(j)).Left = Controls("TB" & CStr(i) & CStr(j)).Left + 10
                ' find position names for ministries at this mass
                ' look at MinistryXMassPositionsRange rows +1 to num of ministries at this mass, col + mass num, look at true, false
                ' if masscol, row = true, then set caption and activate item
                ' also need to see what the value is for the individual user (selected or not selected)
                ' set value of ministry serve grid items
                If minToggleButton.Value = False Then
                    Controls("CheckBox" & CStr(i) & CStr(j)).Visible = True
                Else
                    Controls("TB" & CStr(i) & CStr(j)).Visible = True
                    Controls("TB" & CStr(i) & CStr(j)).Object.Value = ""
                End If
            Next j
            ' find number of positions for this ministry
            NumPositions = 0
            For j = 1 To MaxNumPositions
                If Cells(StartRow + j, StartCol).Value <> "" Then
                    NumPositions = NumPositions + 1
                End If
            Next j
            LeftPos = 0
            If NumMasses < MaxNumMasses Then
                LeftPos = Controls("TB1" & CStr(NumMasses + 1)).Left
            Else
                LeftPos = Lbl11.Left
            End If
            If LeftPos + NumPositions * 24 <= Lbl620.Left Then
                CenterTop = True
            End If
            For j = 1 To NumPositions
                ' set value of positions serve grid items
                If minToggleButton.Value = True Then
                    Controls("Lbl" & CStr(i) & CStr(j)).Visible = True
                End If
                Controls("Lbl" & CStr(i) & CStr(j)).Object.Caption = Cells(StartRow + j, StartCol).Value
                Controls("Lbl" & CStr(i) & CStr(j)).Left = LeftPos
                LeftPos = LeftPos + 24
                If LeftPos > Lbl620.Left Then
                    LeftPos = Lbl11.Left
                End If
                If CenterTop = True Then
                    Controls("Lbl" & CStr(i) & CStr(j)).Top = Controls("TB" & CStr(i) & "1").Top + 3
                End If
            Next j
        Next i
        minTBFirstName.Enabled = False
        minTBLastName.Enabled = False
        minTBPhone.Enabled = False
        minTBEMail.Enabled = False
        minTBFirstName.Value = ""
        minTBLastName.Value = ""
        minTBPhone.Value = ""
        minTBEMail.Value = ""
        minLBMinisterWith.Clear
        minLBOtherNames.Clear
        minLBMinisterWith.Enabled = False
        minLBOtherNames.Enabled = False
        minCBRemove.Enabled = False
        minCBAdd.Enabled = False
        minDeleteButton.Enabled = False
        minOKButton.Enabled = False
        minOKAndEditButton.Enabled = False
        minAddButton.Enabled = True
        FrameMinisterData.Enabled = False
        FrameOthers.Enabled = False
        LFirstName.Enabled = False
        LLastName.Enabled = False
        LPhone.Enabled = False
        LEMail.Enabled = False
        minLblClick1.Enabled = False
        minLblClick2.Enabled = False
        minLblClick3.Enabled = False
        minLblClick4.Enabled = False
        minLblClick5.Enabled = False
        minLblPercAvail.Enabled = False
        Sheets("Setup Sheet").Select
        ' add user names to lists
        Call SortByLastName
        Sheets("People Data Sheet").Select
        minCBSelectMinister.Clear
        For x = 1 To Range("NumberOfMinisters").Value
            If Cells(HeaderRow + x, PeopleHeaderCol).Value <> ", " Then
                minCBSelectMinister.AddItem Cells(HeaderRow + x, PeopleHeaderCol).Value
            End If
        Next x
        Sheets("Setup Sheet").Select
    End Sub
    

    Open in new window

    0
     
    LVL 45

    Expert Comment

    by:patrickab
    malcolm29,

    Please upload your file.

    Patrick
    0
     

    Author Comment

    by:malcolm29
    Here's the file.  I can pretty much get it to start throwing the error by doing the following:  open and enable macros.  Then go to the Modify Ministers tab.  If it doesn't error out, close the program by clicking on the X and then close Excel and save the file.  Saving the file seems to be important.  After a few times, it should start throwing the error.  You can get rid of the error by removing/exporting the MinisterScheduleForm and then importing it back in.  Thanks for your help! Liturgical-Ministry-Scheduler-2..xlsm
    0
     
    LVL 45

    Accepted Solution

    by:
    malcolm29,

    Are you sure that you are referencing the correct Multipage and their subpages? The subpages are not in order and personally I find it pretty confusing.

    I believe that much tighter referencing of the Multipage and its subpages may well overcome the problems you are encountering.

    Patrick
    0
     
    LVL 45

    Expert Comment

    by:patrickab
    BTW - where is MultiPage2? You have code for it but nothing in the project.
    0
     

    Author Comment

    by:malcolm29
    MultiPage2 is on the Configuration tab.  It works fine.  It's only the ModifyMinisters tab on MultiPage1 that causes the problem.  Note that this works fine most of the time, but that I can get it to break as described.  Is there a more explicit way to reference the pages?  I'd be happy to try that out as I agree that might be the issue.  However, please note that this issue only seems to occur for the Modify Ministers page.  Thanks again for your help, Patrick.
    0
     
    LVL 45

    Expert Comment

    by:patrickab
    I reference them like this:

    If UserForm1.MultiPage1.Pages(0).OptionButton1 = True Then
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) = "Page 1, Selection #1"
    End If

    and it works without error.

    Patrick
    0
     

    Author Comment

    by:malcolm29
    Thanks, I will try this out today and see if it works. I appreciate your help.
    0
     
    LVL 45

    Expert Comment

    by:patrickab
    malcolm29,

    The error is almost definitely coming from this routine:

    Private Sub MultiPage2_Change()

    MultiPages are confusing because it is actually irrelevant if they are placed one on top of another. All that happens is the the pages are numbered incorrectly if placed at design time one on top of another - as I think you've discovered already.

    In the Configuration MultiPage you have used an inconsistent naming convention for the different pages (perhaps that's not important?). The first page as you know is always (0) and if you have a control on a page then that control needs to be referenced specifically. I don't think the 'MultiPage2.Value' actually captures what you want as it looks vague to me. If it was 'If UserForm1.MultiPage2.Pages(0).OptionButton1 = True Then' it traps that value but I can't see what 'MultiPage2.Value' traps - so to speak.

    Patrick

    0
     

    Author Comment

    by:malcolm29
    Patrick, thanks for your help.  Here's how I'm using MultiPage1.value and MultiPage2.value.  When someone clicks on the tab for the page, I have the controls on that page get initialized.  So, the .value tells me which page on the control they clicked on.  Then, I have an initialization routine set up all the controls on that page.  I will look into MultiPage2, but this error occurs and only occurs on MultiPage1.Pages(2), the 3rd tab from the left, named "Modify Ministers."  My sense is that it occurs in one of the two procedures shown above.  I have more specifically qualified all references to a control in the MultiPage1_Click procedure, and I will also do that for the minUserForm_Activate procedure (which initializes the controls on MultiPage1.Pages(2)).  Thanks for your help so far in getting me through this.

    By the way, simply qualifying references in the MultiPage1_Click procedure did not resolve the issue.  I'm still working on doing so in the minUserForm_Activate procedure.
    0
     
    LVL 45

    Expert Comment

    by:patrickab
    malcolm29,

    I've been experimenting and have come across some really odd behaviour of MultiPages. Have a look at the attached file and press the button to show UserForm1. Having done that click on any of the MultiPages and you will see some odd numbers. They are the index numbers for the MultiPages and are the same as the MultiPages.Values. Now that is very odd that they are NOT numbered (0), (1) etc. Looks all very confusing. I dunno whether that throws any light on your issue but I think it might begin to explain things.

    Might be worth inserting a few MsgBoxes to trap what's actually happening when a MultiPage is clicked in your wb.

    Patrick
    multi-page-trials-01.xls
    0
     

    Author Comment

    by:malcolm29
    I took a look to better understand what you were saying about the pages not being in order, and I see what you were saying.  I've renamed the pages to match their Index numbers in this latest upload.  I have also more explictly referenced controls in MultiPage1.  However, I can still get the error to occur as described above (closing down the form with the X in the upper right corner, and then exiting Excel and saving the form -- do this a few times and eventually you'll get the error).  Thanks for your continued help. Liturgical-Ministry-Scheduler-2..xlsm
    0
     
    LVL 45

    Expert Comment

    by:patrickab
    malcolm29,

    Xover of comments between us. I guess you haven't yet had time to look at my latest wb. It is worth a look...

    Patrick
    0
     

    Author Comment

    by:malcolm29
    Thanks for sticking with this, Patrick.  When I run your macro, it appears to me that things work similarly to the page's value.  For example:
    UserForm1.MultiPage1, Page 1: Index =0, MsgBox says "Page10"
    UserForm1.MultiPage1, Page 2: Index=1, MsgBox says "Page11"
    UserForm1.Multipage2, Page 1: Index = 0, MsgBox says "Page20"
    UserForm2.MultiPage2, Page 2: Index =1, MsgBox says "Page21"

    So it appears that the message box is displaying what I would have thought: "Page1" or "Page2" followed by the Index number or value of the page within the MultiPage control.

    Now, since my only exposure to MultiPages is within this single application that I've written, I am not sure how they are SUPPOSED to act, so I'm probably biased in terms of having had my expectations set by how they act in Excel.

    Any other thoughts?  It appears that the error gets thrown somewhere between the call in the first procedure "Call minUserForm_Activate" and before the line "minOKButton.Caption = "OK and Done"" in the minUserForm_Activate procedure.  It's almost like it's in the act of calling minUserForm_Activate that something in Excel, and outside of the code, gets confused.

    I'll keep plugging away with your guidance.
    0
     

    Author Comment

    by:malcolm29
    Patrick, I think I found the problem.  I had taken your advice to more explictly reference controls.  I thought I had done so with every reference, but I missed one.  When I fixed that one, I stopped being able to replicate the error.  I am going to play with it for just a little more today and tomorrow, and if the error is gone, I will give you full credit for leading me to the solution.  I sure wish Excel would throw a less generic error that I could track down a little easier!  Thanks so far!
    0
     
    LVL 45

    Expert Comment

    by:patrickab
    malcolm29,

    Pleased to hear you're making some progress. Let me know how it goes after your trials tomorrow. I'm out all day tomorrow - back in the evening.

    Patrick
    0
     

    Author Comment

    by:malcolm29
    The bottom line was that every reference in the procedure to a control on the form had to be fully qualified.  Once I did that, the ambiguous error went away.  Thanks for all your help!
    0
     
    LVL 45

    Expert Comment

    by:patrickab
    malcolm29,

    Pleased to hear that you cracked it. I thought the problem was references that were not explicit.

    Thanks for the grade.

    Patrick
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    875 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now