Complications - Interactions with other WorkBooks

I have an application that is controlled from a UserForm. The user only sees the form - not the worksheets in the background. I start the application with.....

Private Sub Auto_Open()
  Application.Visible = False
  UserForm1.Show vbModeless
End Sub

Open in new window

There seems to be some unfortunate side effects that I was not expecting... for example...

If I open my application - the UserForm is sitting there - just fine. If I now open another Excel spreadsheet that opens fine too. However when I close that spreadsheet it wants to close my application too! Not what I want at all...


If I have a spreadsheet open, and it has a cell selected for editing then my application won't open until the editing is complete. And then my application will open, but the spreadsheet becomes hidden.

Hmmm... There must be a better way.

How do I fix this?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
The best way is not to hide Excel rdpecially if you do not have a good knowledge of Excel. For instance why are you using Auto_Open ? This was replaced years ago by the WorkBook_Open event.

Why do you want to hide Excel?
Is the UserForm supposed to work with the visible workbook?.

If the userform is supposed to interact with the other workbook then crate an addin yhat contains the userform and it will be available to any open workbook. You would need to create a way to call the userform, the best way would be to add a button to the Ribbon

Create an addin

Change the Ribbon
RzzBAuthor Commented:
Roy - Thanks for your help. OK - A note on what I'm doing - sorry it's a bit long.

I do not want the user to have access to the worksheets. I want to control how the worksheets are updated so no errors can occur . Everything is controlled through the UserForm.

The worksheets containing the data are held in a workbook that is external to the workbook containing the code. This enables me to update the code without touching the data.

Every action the user makes is is logged in another workbook.

So there are three workbooks - the Data, the Log and the Code.

At Code startup the data workbook is opened in it's own application (hidden) and the data worksheets copied in to Code workbook. Then the Data application instance and the workbook are closed. The worksheets in the Code workbook are updated by the user through the UserForm and when the user hits a save button they are copied back to the Data workbook.

Also at startup the Log workbook is opened in it's own application (hidden) and remains open until the Code workbook is closed.

There are other actions available to the user on the UserForm, such as mass/individual personalised emailing using Word eMailMerge, mailmerge printing of envelopes and letters, creation of PDFs, archiving, searching etc.

Most of it is implemented and working and being tested, with the exception of the personalised letters. I'm having trouble understanding how to embed word into excel - I think I'm going to have to think of a different way of doing this. Painful - all because some people don't have email accounts!!!!

I did a lot of Access VBA before I retired in 2004 but haven't touched it since. So I'm probably a little out of date and very rusty. I have forgotten most of what I knew! And the grey matter is not quite a quick as it was :-) This application is all written procedurally. I'm already thinking about rewriting using OO - I wish I had bitten the bullet when I started it and gone the OO route.

Not sure if the Auto_Open came from my "out of date" grey matter, or from a Google search. I will investigate WorkBook_Open event.

Pics of the UserForm attached - there are further tabs that become available when the Renewal button is clicked. Membership renewal time that is... all gets exciting then.

Roy CoxGroup Finance ManagerCommented:
The simplest way would be to have one workbook but use the VeryHidden property of the relevant data sheets to hide these sheets. No-one can then access them.

You can also use my addin suggestion

I can see no advantage to writing into one workbook then copying to the master unless you intend trying to have multiple users.

From the images it looks like you are using a ListView control, these can be difficult to code.
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

RzzBAuthor Commented:
Roy thank for your help with this - much appreciated.

Firstly I'm not using ListView control - I'm using ListBoxes - they are used to get a view of all the data, to make a selection for editing and in one case to adjust the ordering of a list. That's all working well.

I think I can see what you are suggesting. Use one workbook for Code and Data. Hide all but one worksheet, and have the userform as the user interface. I guess there are ways to perhaps lock the userform to the open worksheet and stop the user from accessing the worksheet? Code update made through the Add-in process.
Hmmm... The fact is I don't want the user to have any access to the workbook at all except through the userform.  Everything can be controlled and checked very carefully - less errors and headaches than we have had in the past when doing this manually. The users are not very Excel aware. I would prefer it if the user only ever saw the userform - no need to ever see a worksheet.

Splitting the Code and Data, as I have it at the moment, means a very simple code update process - replace the code workbook with a new one. I guess that would be the same with the Add-in process once it's set up.

It is actually all working quite nicely with the exception of this start up complication and the interfacing with Word. Hmm - perhaps I should be using Access :-(

RzzBAuthor Commented:
Oh - I think I have found a solution to my problem!

I have created a workbook whose only job is to start the Code workbook in it's own instance of Excel. That way I get no interference from workbooks that have been started manually.

The code in the new workbook is ...

Private Sub Workbook_Open()

  Dim strExternalWBPathName As String
  Dim xapApp As Excel.Application
  Dim wbkMembershipData As Excel.Workbook
  If CheckForDebug Then
  End If
  'Build string and path of external workbook
  strExternalWBPathName = ThisWorkbook.Path & "\CFAG Membership.xlsm"
  'Get a new Application object - because we want to hide the workbook
  Set xapApp = CreateObject("Excel.Application")
  'Hide the application and workbook when it opens
  xapApp.Visible = False
  'Open the workbook
  Set wbkMembershipData = xapApp.Workbooks.Open(strExternalWBPathName)
  'Tidy up
  Set xapApp = Nothing
  'Close this workbook - don't need it any more
  If Workbooks.Count < 2 Then
  End If

End Sub

Open in new window

Is there anything seriously wrong with this?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
An addin would be the best way to go. The addin remains invisible and the userform can be used on the data workbook.
RzzBAuthor Commented:
OK - Many thanks for your advice.
I have things mostly running at the moment so will continue with it as it is.
Once I get a little time I will make the changes to implement as you recommend.
Roy CoxGroup Finance ManagerCommented:
Post back if you want help with the implementation
RzzBAuthor Commented:
OK - Many thanks. I really don't have time at the moment - Retirement seems to be just a tad more stressful than when I was working... :-) However - perhaps you could just answer one question for me so I can start things kicking around in my brain...

Given that I want all interaction with the user to be through the UserForm, and not really see, let alone have access to a woksheet, or ribbons etc - how do I manage this?
Roy CoxGroup Finance ManagerCommented:
As I said earlier hide the worksheets using VeryHidden and it would require VBA to unhide those sheets.

You can hide the Ribbon or develop your own Ribbon to allow only commands that you want such as displaying your userform.

It is possible to hide the Ribbon as well using VBA, see the example
Roy CoxGroup Finance ManagerCommented:
This example displays a sheet that you can customise with a logo or dashboard
RzzBAuthor Commented:
OK - Many thanks. It will be a few weeks or more before I can put some time to this. I had a quick look - the first workbook get an error in the Workbook open ...

   ActiveWindow.DisplayFormulaBar = Not ActiveWindow.DisplayFormulaBar

Open in new window

My system doesn't seem to know about DisplayFormulaBar . Am I perhaps missing a Reference?
Roy CoxGroup Finance ManagerCommented:
I've re-written the code.
RzzBAuthor Commented:
OK - Thanks Roy - I'll take a closer look in a while. Probably after Christmas now!!
Roy CoxGroup Finance ManagerCommented:
The OP has said that he will post back after Xmas

The points are being awarded to the OP?
RzzBAuthor Commented:
Hmm - Yes indeed.. I want to take another look at this in the new year. I can always start another thread I suppose and reference this one.

If it is closed - does it still remain visible? There is a lot of very useful information in here that I would like to come back to..

NB - I have zero interest in points. Roy (Cox that is) has been very helpful - should I just accept as a solution even though it I don't really see it as one at the moment?

Roy CoxGroup Finance ManagerCommented:
I would rather assist you with solving this. If you start again message me  with a link to this one.
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.