VBA User form corrupting file?

[Kra]

Hi all,

recently I created an user form in PERSONAL.XLSB with 4 command buttons. Half of them change name of current file and save it in specific folder, the rest opens specific file. Since I added this form I have a problem with my excel. Every time I close any workbook, it takes some time to do it (like 3-5 seconds). Then excel restarts itself with empty workbook and shows previously saved workbook as a recovery file. Is it possible that some of this macros is corrupting excel?

Private Sub SaveTicket_Click()

'    MACRO TO ADD TICKET NUMBER TO AN ACTIVE FILE


    Dim NewName As String
        NewName = InputBox("Insert Ticket Number", "Change file's name")
        
    Dim CurrentName As String
        CurrentName = ActiveWorkbook.Name
    
    Dim WS As Workbook
    Set WS = ActiveWorkbook
    
    WS.SaveAs "C:\Local\Tickets\" & NewName & " " & CurrentName
    
   
    Unload Me
    
End Sub

Private Sub SavePrices_Click()
    
    Dim FName As String
    
    
        FName = "C:\Local\Prices\Prices " & _
            Format(Date, "YYYYMMDD") & ".xlsx"
            
        ActiveWorkbook.SaveAs Filename:=FName, _
                              FileFormat:=xlOpenXMLWorkbook
                              
    Unload Me
        
End Sub

Private Sub OpenSalesPrices_Click()
   
   Dim MyPath As String
   Dim MyFile As String
   Dim LatestFile As String
   Dim LatestDate As Date
   Dim LMD As Date

    MyPath = "C:\Local\Prices\"

    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"

    MyFile = Dir(MyPath & "*.xlsx", vbNormal)

    If Len(MyFile) = 0 Then

        MsgBox "No files were found.", vbExclamation

        Exit Sub

    End If

    Do While Len(MyFile) > 0

        LMD = FileDateTime(MyPath & MyFile)

        If LMD > LatestDate Then

            LatestFile = MyFile

            LatestDate = LMD

        End If

        MyFile = Dir

    Loop

    Workbooks.Open MyPath & LatestFile
    
    Unload Me

End Sub

Private Sub OpenSetToOb_Click()

    Workbooks.Open "C:\Local\Personal\OB.xlsm"
    
    Unload Me

End Sub

 

[rlv01]

Well, the obvious first move would be to removet the 4 new macros from your PERSONAL.XLSB and see if the problem goes away.

I am troubled by the use of Unload Me in each of your macros. That's usually something you put in form code to unload the form, not in a macro in a standard code module your PERSONAL.XLSB.

If you are doing the latter, then it may be causing problems. Is Unload Me serving any function? Can you remove it?

 

[Kra]

Well, the obvious first move would be to removet the 4 new macros from your PERSONAL.XLSB and see if the problem goes away.

I am troubled by the use of Unload Me in each of your macros. That's usually something you put in form code to unload the form, not in a macro in a standard code module your PERSONAL.XLSB.

If you are doing the latter, then it may be causing problems. Is Unload Me serving any function? Can you remove it?

I have a user form with 10 command buttons. I need the user form to disappear after each button is used. I have no idea how to do this without using

Unload me

in all of them.



I deleted the user form and the problem is gone. I created it again and the problem comes back so it's something in macros.

 

[rlv01]

If you want to resolve this mystery, you need to do more experimentation.

What happens if, as an experiment, you remove all Unload Me statements and use the "X" in the upper right-hand form window to close the form? And by "what happens", I mean does it resolve the problem?

What happens if, as an experiment, you replace Unload Me with me.hide?

What happens if, as an experiment, you add a 10 second wait statement before each Unload Me statement

Application.Wait(Now + TimeValue("0:00:10"))
Unload Me