excel file(.xlms) continue to be loading but not opened. while loading it, however, it is opened, if another excel file is clicked for being opened

Question

I have just learned VBA and I am working on making an optimized scheduling program.

My program process is as follows.

My program first opens the opening window (userform ufSplash) for about 2 seconds, and then goes to the login window (userform ufLogin). And application visible is false. (related code below (1), (1-1))
after user login normally, it is unloaded and the project setting window (userform ufProjectSet) is opened. (related code below (2))
After the user input value in the project setting window, it is hidden (not unload) and the algorithm setting window (userform ufOptAlgoSet) is shown. (related code below (3))
when the command button confirm in the algorithm setting window is clicked, the userform is hidden and the optimizing algorithm is performed (module main). And in the module Main, progress window(userform ufOptProg) is shown with vbModeless for showing progress of optimizing algorithm (10%, 20%... and so on), of which percentage calculation code is included in the procedure geneticAlgorithm. (related code below (4), (4-1))

My problem is as follows. If debugging one line at a time, all code is normal.

My Excel file sometime(?) open normally but mostly it continues to be loading but not opened at all, or until process(1)'s userform ufSplash, or until process(1-1)'s userform ufLogin. During loading it, however, it is opened if another excel file is clicked for be opened. All other files open normally, only my file has the problem. Also, if I copy my file, the copied file is almost not opened with the above problem. I try to change Application.Visible = True, but it is the same. Also, I try to delete ufSplash, or ufSplash and ufLogin, but it is sometimes the same as the problem.
in the above process (4), the progress window is shown, but nothing on the window. after the optimizing algorithm is finished, it shows contents normally.

The userform ufProjectSet has very many controls, about 600 EA. would it be a problem? Another computer (Office 2015) has the same problem with my file. (My computer Office 2013)

My code is as follows. If somebody wants my all code, I can send Excel file. Please ask experts to solve my problems.

(1) First, when my file starts, the below event is initiated.

Private Sub Workbook_Open()    
    Application.Visible = false

    ThisWorkbook.Worksheets("Initial Setting").Visible = True
    ThisWorkbook.Worksheets("Cycle Schedule").Visible = True
    ThisWorkbook.Worksheets("Log").Visible = True
    ThisWorkbook.Worksheets("Report").Visible = True
    ThisWorkbook.Worksheets("DB").Visible = True
    ThisWorkbook.Worksheets("UserInfo").Visible = True

    Dim dtDelay As Date
    dtDelay = Now

    ufSplash.Show

    If Now < (dtDelay + TimeSerial(0, 0, 2)) Then
        Application.Wait dtDelay + TimeSerial(0, 0, 2)
    End If

    Unload ufSplash
    ufLogin.Show  
End Sub

(1-1) The userform uflogin code is as follows.

Private Sub btnClose_Click()
    Application.Quit
End Sub


Private Sub btnConfirm_Click()
    Dim loginData As Range
    Dim pwd As String
    Dim name As String

    Set loginData = ThisWorkbook.Sheets("UserInfo").Range("A2:C3")

    On Error Resume Next

    pwd = Application.VLookup(Me.txtId, loginData, 2, 0)
    name = Application.VLookup(Me.txtId, loginData, 3, 0)

    If Err.number <> 0 Then
        MsgBox "The corresponding ID does not exist.\n Please check again.", vbCritical, "ID Failure"
        Exit Sub
    End If

    If pwd = Trim(Me.txtPwd) Then
        MsgBox "Login success.", vbInformation, "Login"
        Unload Me        
    Else
        MsgBox "Password incorrect.", vbCritical, "Login Failure"
    End If

    ufProjectSet.Show
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFromControlMenu Then    
        MsgBox "x button does not work"        
        Cancel = True        
    End If
End Sub

(2) The userform ufProjectSet code is as follows.

Private Sub btnCoreActNext_Click()
    Me.Hide  
    ufOptAlgoSet.Show
End Sub

(3) The userform ufOptAlgoSet code is as follows.

Private Sub btnAlgoConfrim_Click()
    Me.Hide
    Call Main
End Sub

(4) The module main code is as follows.

Public Sub Main()
    Application.ScreenUpdating = False
    ufOptProg.Show vbModeless
    Call InitialScheduling    
    Call geneticAlgorithm     
    Unload ufOptProg 
    ufResult.Show         
End sub

(4-1) The userform ufOptProg code is as follows.

Private Sub UserForm_Initialize()
    BarWidth = 0
    Me.lbProgressbar.Caption = "Preparing for optimization......"
    Me.progressBar.Width = BarWidth        
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Unload Me
End Sub

Taehoon Kim Taehoon Kim · Accepted Answer · 2018-11-14T12:26:36

------- Recently Post --------

I had guessed that I solved the problem, but the file repeats to occur the same problem. I found new solutions about (1). through the below URL, I solved my problem(1), but someone didn't know why the problem happened....

https://www.purplefrogsystems.com/blog/2015/06/excel-crashes-when-enabling-macros/

------- Past Post --------- I'm a questioner for this question. I found why the problem (1) happens. The problem was very simple...it was in the first event "Workbook_open()". Some of codes should be revised as follows;

uflogin.show -> uflogin.show vbmodeless

it is because the event "workbook_open" should be finished to last code "end sub" for starting excel file. so, the event can finish the last line "end sub" by showing userform "uflogin' with vbmodeless.

Private Sub Workbook_Open()    

Application.Visible = false

ThisWorkbook.Worksheets("Initial Setting").Visible = True
ThisWorkbook.Worksheets("Cycle Schedule").Visible = True
ThisWorkbook.Worksheets("Log").Visible = True
ThisWorkbook.Worksheets("Report").Visible = True
ThisWorkbook.Worksheets("DB").Visible = True
ThisWorkbook.Worksheets("UserInfo").Visible = True

Dim dtDelay As Date
dtDelay = Now

ufSplash.Show

If Now < (dtDelay + TimeSerial(0, 0, 2)) Then
    Application.Wait dtDelay + TimeSerial(0, 0, 2)
End If

Unload ufSplash
ufLogin.Show  

End Sub

excel file(.xlms) continue to be loading but not opened. while loading it, however, it is opened, if another excel file is clicked for being opened

1 Answers