Excel VBA error: Microsoft Excel Has Stopped Working

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?

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

Select allOpen 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

Select allOpen in new window

malcolm29,

Please upload your file.

Patrick

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

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

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.

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

Thanks, I will try this out today and see if it works. I appreciate your help.

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

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.

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

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

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

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.

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!

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

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!

malcolm29,

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

Thanks for the grade.

Patrick