How to have two open workbooks with just one visible

When we switched from Excel 2010 to 2013, we noticed intense screen flashing while long macros created in Excel 2010 were running even though the macro began with
Application.ScreenUpdating = False

Open in new window

and ended with
Application.ScreenUpdating = True

Open in new window

I found out that this code no longer allows me to have two open workbooks where the one we open manually is visible and the other we open by VBA is not visible. This was due largely to my use of code like
Workbooks(1).Sheets(intCounter).Activate

Open in new window

to switch between workbooks. I've learned that using code like
Workbooks(1).Sheets(intCounter).Range("C5:H5").Value = strPONumber

Open in new window

resolves the flashing problem.
The one problem with still remains is how to hide the opening of the second workbook from the user while still allowing them to see the changes that are happening in the first workbook. In the first workbook I'm using code like
Application.StatusBar = strMsg

Open in new window

to let them know how the macro is progressing.
Every solution I have tried either gives a big white "flash" while the second workbook opens and is hidden, or completely disables both workbooks so that the status bar shows nothing and the program appears to be "locked" until the macro finishes.
Surely using Excel like this is so common that there has to be a solution. But everything I've been able to find so far may work on other versions of Excel, but not 2013.
I'd appreciate some help.
maderitetechAsked:
Who is Participating?
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.

rspahitzCommented:
Another possible solution is to bypass the Excel interface directly and treat the workbook as an object.  The code is not trivial but would look something like this:

    Dim wkbk As Workbook
    Set wkbk = Application.Workbooks.Open("your file here.xls")' you may need to app a parameter to identify the format
    wkbk.Sheets(1).Value = ""' here's where you'd manipulate the workbook
    wkbk.Close
    Set wkbk = Nothing

Open in new window


I'm not sure, but you may also have to add to the end:
    Application.Quit
But I'm not sure if that quits all workbooks or only the one you opened.
0
maderitetechAuthor Commented:
I'm not familiar with bypassing the Excel interface directly and treat the workbook as an object. I found another solution by using a userform to update the status of the macro.
0

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
maderitetechAuthor Commented:
My code already had the following for opening the workbooks. This may be in part what rspahitz was suggesting. WB1 is the visible workbook, WB2 the one I want to hide.

Dim app As New Excel.Application
Dim WB1 As Excel.Workbook
Dim WB2 As Excel.Workbook
Set WB1 = ThisWorkbook
Set WB2 = app.Workbooks.Add("my_hidden_excel_workbook")

Open in new window

0
maderitetechAuthor Commented:
I intended to use the status bar to update macro progress, but the userform works just as well, allows me to keep only the workbook I want visible while the other one is hid. I did not use any suggested solutions. I do not know if they would have worked.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.