Problem after Hiding Excel Workbook behind a VBA UserForm

[james.benham]

Sorry its a long post.....

Hi All,
I'm having a few problems with a UserForm I'm trying to create. This userform runs, with the excel sheet it is running from hidden behind.

Below I have summarised the form itself, the problem I have encountered and solutions I have already tried. Any input would be infinitely helpful.

Summary of Form
- A project manager's dashboard
- It is meant to look like a standalone application that runs in the background of the users machine on startup
- Essentially it is a form with a bunch of buttons on, the captions and URLS of these buttons are stored in the only worksheet ("Dashboard Settings") of the workbook ("PMDash.xls").
- The reason for this is so the captions and links can be changed by someone not acustomed to working with MVB editor.
- Means that user can click a link and it opens a prespecified file location.
- On computer startup the excel book opens, hides itself and then runs the useform "dashboard".

Problem
I use the following code in the workbook:

Private Sub Workbook_Open()
Application.Visible = False
dashboard.Show
End Sub

... in order to hide the settings sheet and run the dashboard.

Everything works fine, the dashboard opens, the application vanishes and the useform remains - looking like a standalone program.

The problem is - if a user opens another excel workbook then it opens in the same application/instance and unhides "PMDash.xls". I want PMDash to remain completely hidden.

Solutions?
A solution I thought up originally was to hide the workbook as well by using:

Windows("PMDash.xls").Visible = False

This does the trick - even if they open another workbook, it opens in the same application window but PMDash.xls remains hidden. This, however, gives rise to a new problem in that if the user then uses the large 'X' to close the application it closes PMDash as well.

My current working solution is that when PMDash is opened for the first time (on startup) it opens a new instance of excel as well. Both the PMDash instance and the new instance (lets call it instance 2) are made to be hidden. I have determined that when a user opens a workbook it is opened in the most recent instance. Doing the above will mean that if they open an existing sheet it will open in instance 2 and therefore not effect PMDash. This gives rise to another problem - if they close that instance then any future files they open will open in the PMDash instance and I am back to square one.

Other possible solutions:
1) Forcing all new files to open in a new instance (VBA only as I do not have access to all users' computers)
2) Working on the basis of my first solution that the sheet remains very hidden, a code that will activate if the instance starts to close and either makes sure that PMDash remains open or that PMDash reopens and hides itself immediately??

Unfortunately this is all a little out of my depth now.

In summary: I need a code that will either prevent a user from accessing a hidden worksheet or a code to ensure they cannot close it.

Any ideas?

Notes:
Operating Windows XP Service Pack 3
Excel Version 2003, although 2007 is also installed.

 

[james.benham]

I don't know if this is too early to bump - but im fearing as it had already dropped by 10 pages.

Thanks

 

[james.benham]

Bump!

 

[Richard Schollar]

Hi James

Off the top of my head, does it improve matters if you go into Tools>Options>General tab and check "Ignore other applications"?

 

[james.benham]

That works!

I can't believe I was thinking of such complicated solutions and it was so simple.

Thankyou soooo much.

James

 

[piynit]

Thi sthing is not working for me... I am facing the same problem.. Can u please share the code that helped you through this... i also want to do the same thing..
* When the user form is opened, the Excel which opened it should be hidden
* If one instance of excel is closed all shouldn't get closed

If you can share the piece of code it would be helpful...

 

[Philippe1990]

Hi,

i just fount this thread as i'm facing the same issue as james descrips, however i wanted to follow up on Richard Schollar replay/fix of this thread,


Becuase when i'm in editor mode, and go into Tools>Options>General tab, i do not have the checkbox "Ignore other applications"?

Can anyone help ?

 

[Maniraj280292]

Hi James,

Have you found any solution to the problem you have discribed here. I am also in need of a solution to the same kind of problem. Please share your views if you have a solution

 

[Crabara]

This is almost a year old thread but this will hide excel upon opening the file leaving a standalone user form. However, you will need to add a command button to show you the excel again or you won't have access to the code unless you press shift upon launching the program or open it with macros disabled.

Private Sub UserForm_Activate()
Application.Visible = False
End Sub