none
Microsoft Excel Stopped Working only after saving the workbook from VBA code RRS feed

  • Question

  • I see this is a similar problem to many, but not an exact match.  I have a Microsoft Excel application using VBA with a userform.  From within the VBA code, the workbook is saved under certain conditions.  I also have SUBs which save the code that run directly from a button on a worksheet.  Those SUBs reside in a module.  I can reopen the workbook fine, but when I click the button to load the userform, I get the dreaded "Microsoft Excel has stopped working...." message and it just hangs.  I have to kill the Excel process to get rid of it.  If I don't save the workbook from within VBA, it works just fine.  Also, if I wait an hour or so, the problem goes away (garbage collection?)  I tried several "On Error" statements, but they don't trap the abend.  I'm at wit's end.  Please help!

    I'm running Microsoft Office 2013 on Windows 7.

    Wednesday, June 8, 2016 5:48 PM

All replies

  • Hi jwagner001,

    you have mentioned that ,"the workbook is saved under certain conditions".

    but you did not mentioned the conditions.

    you mentioned that you have a sub and you called this sub through button and it reside in module.

    why don't you place your code on buttons click event.

    the issue you have mentioned that when you click on button to call userform excel hangs.

    please try to use code below to open user form on button click

    Private Sub CommandButton2_Click()
    UserForm1.Show
    End Sub

    if you still get error then I would recommend you to try only open a user form on button click event in newly created excel file.

    so that we can narrow down this issue.

    if it open successfully then I think that the problem is with your code.

    please try to provide your demo code that reproduce this issue on our side so that we can help you further.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, June 9, 2016 7:19 AM
    Moderator
  • Hi jwagner001,

    is your issue solved or you are still having a problem regarding that issue.

    since you created this thread you did not give any response to our replies.

    if your issue solved would you like to share the solution with our community so that if some other member have same issue like you also get solution by your post.

    if you still stuck and problem is not solved then please let us know so that we can provide you further help.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, June 15, 2016 9:22 AM
    Moderator
  • Hi, Deepak.  Here's the rub:  This only happens on my company issued laptop and, I might add, on my colleague's company issued laptop.  In both cases, it does not happen on our home machines.  I was waiting for my company to convert to Excel 2016, thinking it might be a maintenance issue, since we were backleveled to 2012 on our company laptops.  We're now on Excel 2016 and it still happens.  What  I did discover is that I can override the error by entering the VBA screen, bringing up the userform source and hitting F5 to load it.  After I do that, I can then hit the button on the Excel worksheet to bring up the userform.  I'll send my workbook. Thanks for looking into this.
    Wednesday, July 6, 2016 4:42 PM
  • HI again, Deepak -

    How do I send you my workbook?

    Wednesday, July 6, 2016 4:46 PM
  • Hi jwagner001,

    you can use free file sharing web sites to share your workbook.

    then you can paste the link of it here so that we are able to access it.

    we not need your original data it is ok that the workbook you will shared is able to reproduce the issue containing dummy data.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, July 7, 2016 6:25 AM
    Moderator
  • hi deepak i am using a vba form in excel but my file crashes after random timing may work good for an hour and may stop working in 10 minutes i have observed that mostly it crashes when i try to save it.

    i lose the data every time it happens. working in form is very easy for me so please see if you can do something for me. i am attaching file please see if there is something wrong with the code.

    my email address is imgulhassan@gmail.com 

    Dim bFlag As Boolean

    Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        Cancel = bFlag
        bFlag = False
    End Sub
    Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If KeyCode = 40 And ComboBox1.ListIndex = ComboBox1.ListCount - 1 Then
            bFlag = True
        End If
      
    End Sub
    Private Sub ComboBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        Cancel = bFlag
        bFlag = False
    End Sub
    Private Sub ComboBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If KeyCode = 40 And ComboBox2.ListIndex = ComboBox2.ListCount - 1 Then
            bFlag = True
        End If
        
    End Sub




    Private Sub CommandButton4_Click()
    ''''''''''' Validation '''''''''''''

    If Me.ComboBox1.Value = "" Then
        MsgBox "Please enter Product", vbCritical
        Exit Sub
    End If
    ''''''''''''''''''''
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("sheet1")
    Dim n As Long


    '''''''''''''''''' Enter the date



    n = sh.Range("A" & Application.Rows.Count).End(xlUp).Row




    sh.Range("A" & n + 1).Value = Me.TextBox1.Value
    sh.Range("B" & n + 1).Value = Me.TextBox2.Value
    sh.Range("C" & n + 1).Value = Me.ComboBox1.Value
    sh.Range("D" & n + 1).Value = Me.ComboBox2.Value
    sh.Range("E" & n + 1).Value = Me.TextBox3.Value
    sh.Range("F" & n + 1).Value = Me.TextBox4.Value
    sh.Range("G" & n + 1).Value = Me.TextBox5.Value
    sh.Range("H" & n + 1).Value = Me.TextBox6.Value
    ''''''''''' MULTIPLY

    '''''''''' Clear boxes

    Me.TextBox3.Value = ""
    Me.TextBox4.Value = ""
    Me.ComboBox1.Value = ""
    Me.ComboBox2.Value = ""
    Me.TextBox6.Value = ""
    Me.TextBox5.Value = "1"
    Me.TextBox1.SetFocus




    End Sub

    Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode <= 41 And KeyCode >= 38 Then
    KeyCode = False
    End If

    If KeyCode = vbKeyBack Then
        If Len(Me.TextBox1) = 4 Then
            Me.TextBox1 = Left(Me.TextBox1, 2)
            KeyCode = False
        ElseIf Len(Me.TextBox1) = 7 Then
            Me.TextBox1 = Left(Me.TextBox1, 5)
            KeyCode = False
        End If

    Else

        If Len(Me.TextBox1) = 2 Or Len(Me.TextBox1) = 5 Then
            'add a slash
            Me.TextBox1 = Me.TextBox1 & "/"
        End If
    End If

    End Sub

    Private Sub TextBox2_Change()

    End Sub

    Private Sub TextBox3_Change()

    End Sub

    Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode <= 41 And KeyCode >= 38 Then
    KeyCode = False
    End If

    End Sub

    Private Sub TextBox4_AfterUpdate()
    If TextBox5.Value = "" Then Exit Sub
    TextBox6.Value = TextBox4.Value * TextBox5.Value
    End Sub
    Private Sub TextBox5_AfterUpdate()
    If TextBox4.Value = "" Then Exit Sub
    TextBox6.Value = TextBox4.Value * TextBox5.Value
    End Sub


    Private Sub CommandButton1_Click()

    ''''''''''' Validation '''''''''''''

    If Me.ComboBox1.Value = "" Then
        MsgBox "Please enter Product", vbCritical
        Exit Sub
    End If
    ''''''''''''''''''''
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("sheet1")
    Dim n As Long


    '''''''''''''''''' Enter the date



    n = sh.Range("A" & Application.Rows.Count).End(xlUp).Row



    sh.Range("A" & n + 1).Value = Me.TextBox1.Value
    sh.Range("B" & n + 1).Value = Me.TextBox2.Value
    sh.Range("C" & n + 1).Value = Me.ComboBox1.Value
    sh.Range("D" & n + 1).Value = Me.ComboBox2.Value
    sh.Range("E" & n + 1).Value = Me.TextBox3.Value
    sh.Range("F" & n + 1).Value = Me.TextBox4.Value
    sh.Range("G" & n + 1).Value = Me.TextBox5.Value
    sh.Range("H" & n + 1).Value = Me.TextBox6.Value
    ''''''''''' MULTIPLY

    '''''''''' Clear boxes

    Me.TextBox3.Value = ""
    Me.TextBox4.Value = ""
    Me.ComboBox1.Value = ""
    Me.ComboBox2.Value = ""
    Me.TextBox6.Value = ""
    Me.TextBox5.Value = "1"
    Me.TextBox1.SetFocus
    Me.TextBox2.Value = Me.TextBox2.Value + 1



    End Sub

    Private Sub CommandButton2_Click()
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""
    Me.TextBox3.Value = ""
    Me.TextBox4.Value = ""
    Me.TextBox5.Value = ""
    Me.ComboBox1.Value = ""
    Me.ComboBox2.Value = ""
    Me.TextBox6.Value = ""
    End Sub

    Private Sub CommandButton3_Click()
    Unload Me
    End Sub

    Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If KeyAscii = 13 Then
    TextBox2.SetFocus
    End If
    End Sub
    Private Sub COMBOBOX1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If KeyAscii = 13 Then
    ComboBox2.SetFocus
    End If
    End Sub
    Private Sub COMBOBOX2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If KeyAscii = 13 Then
    TextBox3.SetFocus
    End If
    End Sub



    Private Sub TextBox4_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If KeyAscii = 13 Then
    CommandButton1.SetFocus
    End If
    End Sub

    Private Sub TextBox5_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode <= 41 And KeyCode >= 38 Then
    KeyCode = False
    End If

    End Sub

    Private Sub ToggleButton1_Click()
        
        If Me.ToggleButton1.Value = True Then
            Me.ToggleButton1.Caption = "Maximize"
            Me.Height = 60
            Me.Width = 102
            
            Me.ToggleButton1.Left = 0
            Me.ToggleButton1.Top = 0
            
            Me.Top = 450
            Me.Left = 0
        Else
            Me.Height = 508
            Me.Width = 608
            Me.ToggleButton1.Top = 0
            Me.ToggleButton1.Left = 530
            Me.Top = 40
            Me.Left = 200
                    Me.ToggleButton1.Caption = "Minimize"
        End If

    End Sub


    Private Sub UserForm_Activate()
    TextBox5.Value = 1
    With Me.ComboBox1
    .Clear
    .AddItem ""
    .AddItem "AIR CONDITIONER"
    .AddItem "FLOUR KNEADER"
    .AddItem "REFRIGERATOR"
    .AddItem "WASHING MACHIN"
    .AddItem "DEEP FREEZER"
    .AddItem "MICRO WAVE OVEN"
    .AddItem "COOKING RANGE"
    .AddItem "GAS STOVE"
    .AddItem "CHOKI FOR FRIDGE"
    .AddItem "JUICER"
    .AddItem "CHARGING FAN"
    .AddItem "HAND BLENDER"
    .AddItem "DRYER"
    .AddItem "VACUUM CLEANER"
    .AddItem "IRON"
    .AddItem "ROTI MAKER"
    .AddItem "SANDWICH MAKER"
    .AddItem "LED TV"
    .AddItem "Market Margin"
    .AddItem "cancelled"
    .AddItem "Room Cooler"
    .AddItem "WATER DISPENSAR"
    .AddItem "FAN CHARGING"
    .AddItem "STABILIZER"
    .AddItem "Meat Grinder"

    End With

    With Me.ComboBox2
    .Clear
    .AddItem ""
    .AddItem "NATIONAL"
    .AddItem "SUPER GENERAL"
    .AddItem "ABDULLAH"
    .AddItem "ORIENT"
    .AddItem "WAVES"
    .AddItem "DAWLANCE"
    .AddItem "ELECTROLUX"
    .AddItem "PUMA"
    .AddItem "INDUS"
    .AddItem "JACKPOT"
    .AddItem "UNIVERSAL"
    .AddItem "TECHNO"
    .AddItem "AHMAD"
    .AddItem "ATLAS"
    .AddItem "SHALIMAR"
    .AddItem "ASIA"
    .AddItem "SUPER ASIA"
    .AddItem "BOSS"
    .AddItem "PANASONIC"
    .AddItem "NEXEN"
    .AddItem "HAIER"
    .AddItem "GREE"
    .AddItem "MITSUBISHI"
    .AddItem "GENERAL TEC"
    .AddItem "SUNSHINE"
    .AddItem "SONICE"
    .AddItem "WEST POINT"
    .AddItem "SAMFORD"
    .AddItem "AXIS"
    .AddItem "CHINA"
    .AddItem "PAK"
    .AddItem "DMB"
    .AddItem "SONEX"
    .AddItem "younas Swabi"

    End With


    End Sub

    Saturday, December 8, 2018 3:45 PM