How to have two open workbooks with just one visible

Posted on 2014-08-04
Last Modified: 2014-08-16
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

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.
Question by:maderitetech
    LVL 22

    Assisted Solution

    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
        Set wkbk = Nothing

    Open in new window

    I'm not sure, but you may also have to add to the end:
    But I'm not sure if that quits all workbooks or only the one you opened.

    Accepted Solution

    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.

    Assisted Solution

    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


    Author Closing Comment

    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.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now