Keep UserForm Visible While Other Workbooks Are Open

  • Hi all,
    I hope that I can get help with this one.
    I have a workbook with 'Application.Visible = False' in the 'Workbook
    open' event, as well as 'Userform1.show'. The relevant Desktop shortcut
    is set up for the application to run 'Minimized' and to start in
    "C:\Program Files\Microsoft Office\OFFICE11".
    In this manner, when the shortcut is Dbl clicked, the userform appears
    with no visible evidence of Excel having been started. The
    application.visible attribute is reset in the 'workbook close' event
    with :"Application visible = true.
    All of this works extremely well, however wilst previously opened
    workbooks remain open and visible when I start this workbook, while the
    workbook is opened I an unable to start any other excel workbook via
    their shortcuts. I can however start a new instance of Excel then
    browse to the workbook to open it.
    MY QUESTION: How do I allow users to start any of their Excel
    workbooks via their shortcuts while my application is running and the
    userform showing?
    Any help will be appreciated

  • Re: Application.Visible = False


    Firstly I would say using Application visible is not a good idea. It can cause many problems not just the ones mentioned but ... have you looked at using the workbook activate / deactivate events rather than the open/close events.


    Carl

  • Re: Application.Visible = False


    If I understand correctly, you need to close the UserForm. You would need to do this whether the application was visible or not, unless you opened the other workbooks via code

  • Re: Application.Visible = False


    Thanks Roy,
    obviously I haven't made my problem very clear.
    My workbook works flawlessly. It contains 7 userforms and the user navigates from one to another in a controlled manner. At the end of the process a new workbook is saved automatically to a predetermined path and my workbook closed without saving while the application is turned back to visible = true. The whole thing works very well with the exeption mentioned in my original post.
    I need to find a way while my workbook is the active workbook (Application.visible = false) to allow users to open their own workbooks (which I have no control over) from their destop shortcuts. As explained in the original post I can open Excel then browse to a workbook and open it, but not form a shortcut.

  • Re: Application.Visible = False


    I,m guessing you are looking for Application.IgnoreRemoteRequests = True to stop other workbooks opening in the excel instance you have hidden.


    Be aware that this setting persists between excel sessions so it is imortant to reset it.
    Try: [vba]Option Explicit


    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'important to reset this
    Application.IgnoreRemoteRequests = False
    End Sub


    Private Sub Workbook_Open()
    'need to use ontime to allow xl to initialise fully
    Application.OnTime Now, "ThisWorkbook.OnlyOneOfMe"
    End Sub
    Private Sub OnlyOneOfMe()
    Dim XlApp As Excel.Application
    On Error GoTo BAD
    With Application
    If Me.ReadOnly Or .Workbooks.Count > 1 Then
    Me.ChangeFileAccess Mode:=xlReadOnly
    Set XlApp = New Excel.Application
    'XlApp.Visible = True
    XlApp.Workbooks.Open (Me.FullName)
    GoTo BAD
    Else
    'stop opening from explorer (but not from excel)
    .Visible = False
    .IgnoreRemoteRequests = True
    UserForm1.Show
    .Visible = True
    .Quit
    End If
    Exit Sub
    End With
    BAD: If Err Then MsgBox Err.Description, vbCritical, "ERROR"
    Set XlApp = Nothing
    Me.Close False
    End Sub[/vba]


    Also be aware that with the Application hidden, the userform will show, and remain on the desktop, but if another application is activated your userform will be covered with no taskbar or system tray icon to select it again.


    Have a look at Ivans site for info on this:
    http://www.xcelfiles.com/Userform_TaskBar.html
    http://www.xcelfiles.com/Userform_SystemTray.html


    Or you could just use some API to set the form OnTop of everything else.

  • Re: Application.Visible = False


    Thanks Insomniac
    you hit the nail right on the head, this has fixed the only problem I had with this application.
    I have tested it and there is no evidence of any problem. I can now open other workbooks from desktop shortcuts while my application is running.
    I hope to be able to help you out one day.
    SimonB: D

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!