Excel error, stops macro run

Question

I am experiencing a strange error in Excel. I have a macro that shows a non-modal user form when I press CTRL + m (Macro shortcut). From time to time, and this is not so often (it shows once or twice during the day, I use a macro every 5 minutes or so), Excel will not run the macro, will not show the user form and will just sound (like in the "error code execution cannot continue ").

I went into the macro window to try to click "Run" and execute manually, but all the buttons are disabled, except for "Create". If you click on it, then the macro name is not valid. As you can see in the screenshot below, the macro name shows the instance where the code is located (Sheet1 books).

Sometimes this can be eliminated by saving the book and simply trying it, but sometimes it is not; when this does not happen, I run another macro (double-click on a specific column), which shows a modal user form and executes its code. Then my first macro returns to normal.

Any help would be greatly appreciated.

Edit: Adding code as requested in comments

Sub ShowCommentWindow() Dim myCell As Range Dim companyColumn As Long Dim wbk as Workbook Dim company as String Dim phone as Long Set wbk = ActiveWorkbook For Each myCell In wbk.Worksheets(1).Range("A1:Q1") If myCell.Text = "Company" Then companyColumn = myCell.Column company = ActiveCell.Text phone = ActiveCell.Offset(0, 4).Value Exit For End If Next myCell If ActiveCell.Column = companyColumn Then If EmailForm.Visible Then GoTo ExitProc Else If Not ActiveCell.Row < 4 Then ActiveWindow.ScrollRow = ActiveCell.Row - 3 Else ActiveWindow.ScrollRow = ActiveCell.Row End If If CommentWindow.Visible Then CommentWindow.AddButton.SetFocus CommentWindow.CommentBox.SetFocus Exit Sub Else CommentWindow.Show ManageComments AddComment End If End If End If ExitProc: End Sub 

Edit2: Posting more code for QueryClose:

 Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) Dim myCell As Range Dim isCompany As String If Not CommentWindow.CommentBox.Text = CommentWindow.TextCopy.Text Then saveConf = MsgBox("Changes have not been saved yet. Do you want to save?", vbExclamation + vbYesNoCancel + vbDefaultButton2, "Save changes?") If saveConf = vbYes Then Call SaveComment GoTo ExitProc ElseIf saveConf = vbCancel Then changed = True Cancel = 1 CommentWindow.AddButton.SetFocus CommentWindow.CommentBox.SetFocus 'CommentWindow.CommentBox.Text = CommentWindow.TextCopy.Text Else CommentWindow.TextCopy.Text = CommentWindow.CommentBox.Text GoTo ExitProc End If Else If Not changed = True Then GoTo ExitProc End If End If ExitProc: End Sub 

Answer 1

It seems that the problem is not Unload(UserForm) forms from ( Unload(UserForm) )
This causes a memory leak.
Even the official documentation - this applies to Access, but should behave the same for Excel (there is no Form document or user form documentation) - indicate that the life cycle is unloaded → Deactivate-> Close, and this should happen when you close the user form, and daily use showed that Unload, if not specified, may not start when you close a user form.
The life cycle is not so strictly controlled, but that can lead to memory leaks and strange behavior, always when working with objects you should not rely on the garbage collector to clear them if not specified. Adding something to confirm that terminate is being processed is likely to be useful.
EDIT
If you are having trouble remembering unloading — or still having memory issues — it is good practice to do the following:

 Sub MyMainProcess() Dim myform As UserForm1: Set myform = UserForm1 'this is your UserForm name myform.Show 'my stuff needed... Unload myform Set myform = Nothing End Sub 

Unload and clean nothing as much as possible with coding

Answer 2

I see that you are calling an "external" macro (it is not included in the active workbook). Is it possible that then, approximately twice a day, when he is not working on this book (Database 2 Lumber.xlsm) is used by someone else at that time (eight starts, or another macro?).

If so, what I did before is to keep a local copy of the workbook every time the macro runs.