Userform related Excel application crash

Hi Leith,

I do not receive any errors in the code. The "error" occurs after resetting/ending the code in the middle of the MakeOrderTbl(Supp as String) subroutine. At this point Excel looks OK, but if I interact with a spreadsheet or formula it behaves oddly, such as the Esc key not functioning (after entering a cell with F2). If I then attempt to save the workbook, Excel stops responding and crashes.

If I run the MakeOrderTbl sub alone, with an actual declaration of the Supp variable (instead of passing it through from the UserForm), it performs as expected, without crashing.

If I run the UserForm_Initialize private sub from within the VBE (clicking the play button), it displays the form with populated combobox. Upon user selection from the combobox and subsequent clicking of the form's CommandButton1, the Supp variable is defined, passed through to MakeOrderTbl(Supp as String) and everything functions fine.

Only when the extra step of initializing/showing the Userform is added (via a Button (Form Control) assigned with the CreatePOUserForm subroutine) does Excel spaz out.

The error information is as follows:
Problem signature:
Problem Event Name: APPCRASH
Application Name: EXCEL.EXE
Application Version: 15.0.4569.1504
Application Timestamp: 52c5ed10
Fault Module Name: EXCEL.EXE
Fault Module Version: 15.0.4569.1504
Fault Module Timestamp: 52c5ed10
Exception Code: c0000005
Exception Offset: 00789991
OS Version: 6.1.7601.2.1.0.256.48
Locale ID: 1033

Additional information about the problem:
LCID: 1033
skulcid: 1033

I have to remove some information from the workbook and then will upload it shortly.

Anyone have any ideas?

If no one has any ideas what is causing the error, are their any suggestions for a better method of using userform variables in separate subroutines? I haven't worked with userforms before so I'm sure there's a better way than passing arguments into called subroutines.

After a little further thought, I believe the Excel crashing that resulted from my initial process may have been due to my order of operations...unloading the userform before calling the outside subroutine and passing the variable (illustrated in my first post but shown here as well):

Private Sub CommandButton1_Click()

Dim Supp As String

Supp = Me.Controls.Item("ComboBox1").Value
Unload Me
Call MakeOrderTbl(Supp)

End Sub

I think that had I used Me.Hide instead of Unload Me, and then waited to unload the userform in the MakeOrderTbl(Supp as String) subroutine like I ended up doing, Excel wouldn't have gotten confused and later crashed. I suspect that even though the code would "work", in that it still functioned to call the sub and pass the argument, Excel wasn't able to actually follow the chain of events since the argument was being passed from something that didn't exist (the userform had already been unloaded). Just a theory though.

I'll leave this thread open for a little while in case someone with a better understanding of all of this wants to chime in. It would be nice to get some input on what is considered the "right" or "best" way to work with user-selected userform variables in outside subroutines.

For those with similar questions in the future, I got the process to work by changing the way I passed the Supp variable.

Instead of using an outside subroutine to show/initialize the userform and then have the userform call another outside subroutine while passing Supp...

A subroutine now shows the userform which declares the user selection as a public variable. The userform is then hidden and is not unloaded until Supp is assigned in the original subroutine. This seems to work and I haven't had any crashes.

Variable declared as public in the userform code:

Option Explicit
Public Supp As String

Private Sub CommandButton1_Click()

Supp = Me.Controls.Item("ComboBox1").Value
Me.Hide

'Unload Me
'Call MakeOrderTbl(Supp)

End Sub

Userform initialized, public userform variable assigned, and userform unloaded as follows:

Sub MakeOrderTbl()

Dim wb As Workbook
Dim wsInv As Worksheet
Dim iRow As Long
Dim iRowLast As Long
Dim RowNum As Long
Dim Supp As String

    uf_1_CreatePO.Show
    If uf_1_CreatePO.Supp = "" Then
        Exit Sub
    Else
        Set wb = ThisWorkbook
        Set wsInv = wb.Sheets("InventoryList")
        
        Application.ScreenUpdating = False
        
            Supp = uf_1_CreatePO.Controls.Item("ComboBox1").Value
            Unload uf_1_CreatePO
            
            'Code continues