none
Excel 2013 VBA Macro , when any workbook is added or closed to the active workbook the Visibilty status of the application is effected RRS feed

  • Question

  • Excel 2013 VBA Macro , when any Workbook is added or closed from the Active workbook theVisibilty status of the application is effected i.e makes Application.visible true. This causes the workbook visible ,hiding the VBA form. And when I change the Visibility status to false after adding and closing of any other workbook ,the whole application visibilty is set to false making the workbook as well as form hidden.
    Monday, November 23, 2015 7:34 AM

Answers

All replies

  • I have written a similar application in Excel and the only solution I've found is to use a 2nd instance for this workbook only, means you need a starter workbook for your special workbook.

    The trick is that this instance has to ignore DDE requests and there is a switch in the Exceloptions for that. Unfortunately this switch is permanent, so we need some code to be sure it is enabled when all worksbooks are closed. (Otherwise a double click in the windows explorer doesn't open any Excel file anymore!)

    Your special workbook should at min. contain this code:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
      Application.IgnoreRemoteRequests = False
    End Sub


    And the starter workbook should contain a code like this:

    Option Explicit
    
    Const YourFilename = "C:\Whatever\File.xlsm"
    Dim xlApp As Application
    
    Private Sub Workbook_Open()
      Set xlApp = CreateObject("Excel.Application")
      xlApp.IgnoreRemoteRequests = True
      xlApp.Workbooks.Open YourFilename
      
      Application.IgnoreRemoteRequests = False
    End Sub
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
      Dim Wb As Workbook
      For Each Wb In xlApp.Workbooks
        Wb.Close False
      Next
      xlApp.Quit
      Application.IgnoreRemoteRequests = False
    End Sub
    

    This works for many years in an environment with multiple users.

    Andreas.


    Monday, November 23, 2015 8:00 AM
  • I don't quite follow what you are doing but there are indeed significant differences with SDI introduced in Excel 2013 compared to MDI in all previous versions. See the following article which, depending on what you are doing, may address your problem -

    Keeping Userforms On Top Of SDI Windows In Excel 2013 And Up

    • Marked as answer by David_JunFeng Monday, December 7, 2015 3:30 PM
    Monday, November 23, 2015 11:04 AM
    Moderator
  • Starting with Excel 2013 (SDI)- The Application.Visible Returns or sets a Boolean value that determines whether the object is visible. Read/write.

    If all windows are hidden:

    • Application.Visible becomes False

    • Setting Application.Visible to True displays all hidden windows

    • Opening a document via the shell only shows that window and Application.Visible is now True

    Additionally:
    • Application.Visible = False hides everything and Application.Visible = True displays everything, ignoring any document-level settings

    • If all of the windows are hidden via the window-level setting then the application-level setting toggles as well

    • Having at least one window displayed means that the application-level setting is True

    To get more information see the following post :

    https://github.com/vdeluna/VBA_Excel_Test/blob/master/en-us/content/596f5bd6-6a03-4d33-8002-f2c083f7bc79.md

    • Proposed as answer by Malick F Monday, November 23, 2015 8:26 PM
    Monday, November 23, 2015 8:26 PM