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
Keep UserForm Visible While Other Workbooks Are Open
-
-
-
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 ExplicitPrivate Sub Workbook_BeforeClose(Cancel As Boolean)
'important to reset this
Application.IgnoreRemoteRequests = False
End SubPrivate 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.htmlOr 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 -
Re: Application.Visible = False
SimonB,
[thread title]*[/thread title]
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!