Extra excel window that will not close in excel 2013 pro

[dloskot]

I have a PESONAL.XLSB sheet with a common macro that is in XLSTART folder. When I open an excel sheet that has local macros and forms. (i.e. just for that sheet). Everything is fine to start with, I can click on the box to make the form open, the form works fine, but when I exit the form excel goes back to the main excel sheet but then opens two other blank sheets that are grayed out and I can't do any thing with them. I can't close them unless I hold the shift and click the X this closes all sheets. This does not happen on machines that have Excel 2010 pro. I have looked all over the internet and can not fine why this is happening. If I go back to the main sheet I have an second box to quite and if I click that and run the macro to shut down all copies of Excel close. If I just click the X the other two sheets stay open and the only way to them is to use the Shift Click of the X, then both close at once. If I open a sheet that doesn't have any local macros it no extra sheets are opened.

If I remove the Personal.xlsb file from XLSTART I don't have any problems.

If I open a file and just run the shared macro from Personal.xlsb there are no issue.

I have the exact same problem on three different systems. Does anyone have any suggestions?

Thanks Doug

 

[bobsan42]

Try this:
Excel Options / Advanced / Display -> check the box next to Disable hardware graphics acceleration

 

[dloskot]

Try this:
Excel Options / Advanced / Display -> check the box next to Disable hardware graphics acceleration

I tried your suggestion, but it didn't make any difference.

 

[bobsan42]

It helped me with a similar symptom once upon ...
See if any of these will help:
https://superuser.com/questions/879...-second-blank-window-when-opening-a-file-from
https://www.thewindowsclub.com/excel-opening-blank-window
https://support.microsoft.com/en-ca...t-excel-opening-blank-when-you-double-click-a

Or search the web for more. Just don't fall for any boosting or optimizing programs.

 

[dloskot]

I have continued to look at this issue and my guess is it has something to do with opening the from and making the application (Excel) invisible, The two blank excel sheets don't show up until I close the from and make the application visible again. Below is the code I use to open form and to close from and make application visible. I tried to see if there was a way to just open the worksheet I stared with but was unsuccessful.

Code to open from and hide Excel sheet

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub Data_Entry_Setup()
Application.Visible = False
KAH_Data_Entry_Form.Show
End Sub[CODE]

[/FONT][/CODE]

Code to close from and make excel visible

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub Cmd_End_Click()
Quit = MsgBox("Do you want to quit?", vbYesNo, "Quit?")
If Quit = vbYes Then
    Application.Visible = True
    Unload Me
End If[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]End Sub[CODE]

I would greatly appreciate if someone could help. Something must have changed between Excel 2010 and 2013 as I don't have the problem in 2010, or different compoents were installed.

[/FONT]
[/CODE]

 

[bobsan42]

After you make the application visible loop through the opened windows and make Invisible the one you don't need.
something like:

for i =1 to application.windows.count 
with application.windows(i)
if .name = ".........." then .visible=false
end with
next i

actually in the same way you can only hide the window rather than the whole application.

Also I would guess if you turn you personal.xlsb into an add-in you will not face this issue.

 

[dloskot]

Bobsan42: I tried your code but ran into some issues.

I added some messge boxes to see what the count was for Applications.Windows.count before and after I ran the Applications.Visible = True command.
So before the Visible = True command the count was 2.
After the visible = True command the count was 0 so the loop didn't run. However if I used task manager it show three windows. My Workbook and two Blank/greyed out Excel windows. This would appear to be a Microsoft problem and not a coding problem.

That said I commented out some of the code so Visible = True didn't run to see if the loop would work and I got a "RunTime error 438 Object doesn't suppor this property or method" on the If Line. I tried several things to fix it but was not successful
Can you suggest how to fix the command you suggested? Below is the code I used.

One last question how do I make Personal.xlsb an add-on? I am not sure exactly what that means or how to do it.

[FONT=Verdana]Sub Cmd_End_Click()
Dim i As Integer[/FONT]
[FONT=Verdana]Quit = MsgBox("Do you want to quit?", vbYesNo, "Quit?")
If Quit = vbYes Then
MsgBox (Application.Windows.Count)
'Application.Visible = True
MsgBox (Application.Windows.Count)
    'For i = 1 To Application.Windows.Count
    For i = 1 To 3
    MsgBox (Application.Windows.Count)
    With Application.Windows(i)
    If Application.Windows(i).Name = ("KAH School & Other Volunteer Entry Form.xlsm") Then .Visible = False
    
    End With
    
    Next i
    Unload Me
End If[/FONT]
[FONT=Verdana]End Sub[/FONT]

Thanks for your help.

 

[bobsan42]

Well you must not hide the application - the idea is to hide only one window. Then make it visible again later when needed.
I was writing directly in the browser so I made a property mistake (instead of .Name it had to be .Caption). This code should work:

Sub Cmd_End_Click()
    If MsgBox("Do you want to quit?", vbYesNo, "Quit?") = vbYes Then
        On Error Resume Next
            Application.Windows("KAH School & Other Volunteer Entry Form.xlsm").Visible = False
        On Error GoTo 0
        Unload Me
    End If
End Sub

BTW it is a good practice (albeit sometimes tedious) to remember to define all your variables and then clear them in the end - helps avoid a lot of mistakes. (Use Option Explicit in the beginning of all your code modules - then upon Compile you will get error messages for undefined variables).

for Add-in: All you have to do is Save the file as Add-in (XLAM) and load it in Excel.
To load it: Go to Developer Tab select Excel Add-ins. If the Add-in is not listed click browse and locate it. Once you get it in the list select the checkbox to load/unload the add-in.
Addins are normally stored in %AppData%\Microsoft\AddIns but it can be loaded from anywhere.

 

[dloskot]

Well you must not hide the application - the idea is to hide only one window. Then make it visible again later when needed.
I was writing directly in the browser so I made a property mistake (instead of .Name it had to be .Caption). This code should work:

Sub Cmd_End_Click()
    If MsgBox("Do you want to quit?", vbYesNo, "Quit?") = vbYes Then
        On Error Resume Next
            Application.Windows("KAH School & Other Volunteer Entry Form.xlsm").Visible = False
        On Error GoTo 0
        Unload Me
    End If
End Sub

BTW it is a good practice (albeit sometimes tedious) to remember to define all your variables and then clear them in the end - helps avoid a lot of mistakes. (Use Option Explicit in the beginning of all your code modules - then upon Compile you will get error messages for undefined variables).

for Add-in: All you have to do is Save the file as Add-in (XLAM) and load it in Excel.
To load it: Go to Developer Tab select Excel Add-ins. If the Add-in is not listed click browse and locate it. Once you get it in the list select the checkbox to load/unload the add-in.
Addins are normally stored in %AppData%\Microsoft\AddIns but it can be loaded from anywhere.

I tried just hiding and unhiding the window and not the application with the code you suggested and I still get the two extra blank / grayed out windows. I have not had time to try the Addins yet. As I said I think this is a Microsoft bug.

 

[dloskot]

I made

personal.xlsb an addin and checked it in the list, but when I opened a new excel sheet the macros in

personal.xlsb were not available, which is the whole reason for having

personal.xlsb in the first place.

Any other suggestions? This has been very frustrating and a royal pain.

Thanks Doug​

​

​