Workbook closes "by itself" when some macros run or other workbooks opened.

[bifjamod]

I have recently begun encountering a situation where my workbook will automatically close when either one or more macros is run, or even when other workbooks are opened. I've been trying to peg down the specific macro issue that could be causing this, but so far reproducing the issue is hit or miss, and I cannot be certain of any one thing. As bad as that is, it keeps happening when I open some other workbooks as well, which are non-macro workbooks (xlsx).

Can anyone offer some suggestions on what steps I might take to try and narrow down the source of this unwanted behavior?

[SamT]

It almost has to be tied to an Event "Macro." These "Macros" all have an underscore (_) in their name.

Ex: Private Sub Selection_Change(Target As Range)

for investigate purposes you can add a line at the top of each Event Sub

"MsgBox "Running (Name of 'Macro' here)"

[bifjamod]

Thanks Sam; it seems to me it almost has to be a Worksheet_Activate event, because when the macro that causing the closure runs, it activates the first sheet in the workbook which then closes (it's not written that way, though.) However, I've just done substantial mouse chasing where I have the workbook open, and then open an xlsx or xls file with no macros; I can almost predictably reproduce the event when I have two other primary workbooks open, but not 100% consistently to identify one of those workbooks as the culprit. Moreover, NONE of the workbooks has a Worksheet_Change event that includes a command to close the workbook. I've combed through each workbook multiple times just today chasing this mouse, with no solution so far.

I will take your suggestion on the msgbox to see if something can be shaken loose.

[bifjamod]

It almost seems like it's when the workbook loses focus that this happens.....I just had a representative sample open, clicked on a different workbook to check a sheet in that one, and -poof- .... workbook closed on me.

[bifjamod]

Alright, I think this might be related to my userform....which is not set to activate except on Workbook_Open, but preliminary testing seems to point the finger here.....

[bifjamod]

OK, still preliminary and not 100% consistently reproduced, but I think I'm getting close. My userform runs at Workbook_Open with this code:

With UserForm1
      .StartUpPosition = 0
      .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
      .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
      .Show
    End With

Now, in the userform is this code:

Private Sub CommandButton1_Click()
    If Not IsSelected(ListBox1) Then
        MsgBox "Please select user."
        Exit Sub
    End If
    If Not IsSelected(ListBox2) Then
        MsgBox "Please select process."
        Exit Sub
    End If
    Me.Hide
End Sub

So now I'm wondering if the Me.Hide command is leaving the userform open and somehow causing this problem when the workbook changes focus.

I'm continuing to test by commenting out the userform show section of the workbook_open event with a set of workbooks I know causes the closure. After saving and existing the workbook, then reopening (thus NOT having the userform alive), the unwanted closure does not happen. Period. Reactivate the userform show event, save, exit and re-open with the userform, and I can once again nearly consistently produce the crash.

[bifjamod]

OK, NOT me.hide. Tried Unload Userform1, crash still occurring. Next.

[bifjamod]

So, it seems that by activating the userform from the Workbook_Open event, this mysterious crash occurs, albeit somewhat randomly (meaning, I can reproduce it about 90% of the time. However, by moving the userform activation back to the Worksheet_SelectionChange event, I can consistently NOT reproduce the crash. Sooooo, being really glad I only commented the Worksheet_SelectionChange out instead of removing it from the master workbook, I am going to restore and see how new files go over the next few days.

Anybody have a theory on why this crash would occur with the userform being triggered from the workbook_open event?

[GTO]

Could you scrub any sensitive data and upload the wb?

[Paul_Hossler]

So, it seems that by activating the userform from the Workbook_Open event, this mysterious crash occurs, albeit somewhat randomly (meaning, I can reproduce it about 90% of the time. However, by moving the userform activation back to the Worksheet_SelectionChange event, I can consistently NOT reproduce the crash. Sooooo, being really glad I only commented the Worksheet_SelectionChange out instead of removing it from the master workbook, I am going to restore and see how new files go over the next few days.

Anybody have a theory on why this crash would occur with the userform being triggered from the workbook_open event?

Try Code Cleaner. I've found that it helps clear up a lot of weird issues

http://www.appspro.com/Utilities/CodeCleaner.htm

During the process of creating VBA programs a lot of junk code builds up in your files. If you don't clean your files periodically you will begin to experience strange problems caused by this extra baggage. Cleaning a project involves exporting the contents of all its VBComponents to text files, deleting the components and then importing the components back from the text files

[bifjamod]

Could you scrub any sensitive data and upload the wb?

No, but in an interesting twist, I thought I would copy the relevant code to a blank workbook and share that. Except, it's now what I *thought* was the relevant code, because initial testing suggests there's more to it than just the userform display. So that has me chasing down a new avenue of investigation, and I thank you for the inspiration. I will share what I can if I can reproduce this in a limited workbook.

[bifjamod]

Try Code Cleaner. I've found that it helps clear up a lot of weird issues

http://www.appspro.com/Utilities/CodeCleaner.htm

Thank for the tip! I'll give that a look.

[bifjamod]

No, but in an interesting twist, I thought I would copy the relevant code to a blank workbook and share that. Except, it's now what I *thought* was the relevant code, because initial testing suggests there's more to it than just the userform display. So that has me chasing down a new avenue of investigation, and I thank you for the inspiration. I will share what I can if I can reproduce this in a limited workbook.

Ultimately, I could not reproduce this problem in a distinctly separate workbook created exclusively for this purpose, nor could I even reproduce the problem in workbooks previously known to experience it. I have since switched all workbooks to the utilize the Worksheet_SelectionChange event. This would not be my ideal approach to the issue, but I'm getting the hang of it remembering to make a selection change before doing any work associated with the transaction.