Workbook won't open in Excel but is visible in Project Explorer

    Hello,


    I have built a simple quotation system that references a master price list to populate the price fields. The user forms all work as they should, but for some reason, I cannot open the price list workbook anymore. When the macros are running, I can see the file in the VBA project explorer open/close as it should. But when I open the price list directly, I see it download from the server, enable the macros, then a blank grey screen is shown on Excel, even though the file is definitely open in the VBA project explorer.


    I have tried a few things so far:

    1) Ensure I haven't hidden the file

    2) Made a copy, renamed, moved file location etc.

    3) Changed my user permissions to have full access


    I haven't altered this workbook and only open it once on my user form using the "GetObject" command to retrieve some cell values. I do need to be able to update this workbook as it is the master price list.


    Is there a problem with the workbook or the way that I am using it here? This is my first VBA project so still learning a lot!


    Thanks

    Hello,


    When you say you cannot open your workbook ...


    1. Is it when you are using a macro from your UserForm ...???

    or

    2. Is it when you are using Excel main menu: File> Open ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    Well, if you can retrieve cell values ... the issue is only related to the fact it is not visible ... right ?


    Do not know which code you are using to open your file ... but is there an event macro in the module ThisWorbook which simply hides automatically your worksheet ..???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    Here is the code I use to reference the workbook I'm later struggling to open/see.


    Later I have made a button purely to open this workbook because I was having some trouble:


    Code
    Sub Button12_Click()

Dim filepath As String
Dim register As Workbook

filepath = "Filepath\Pricing Register.xlsm"

Set register = Workbooks.Open(filepath)

End Sub


    The code runs without error but won't display the workbook.


    However, I can see the sheets, macros etc, for the Pricing Register workbook in the VBA project explorer. It's the same when opening the file directly via the File > Open menu.

    So, you should test the following procedure :

    1. Start with opening Excel (only Excel…, not your workbook)
    2. Hold down the keyboard Shift Key
    3. Then Click on File > Open
    4. Click on your file: Pricing Register.xlsm
    5. Release the Shift Key… only after your workbook is opened …

    This method allows to Open an Excel File ...while skipping the two events : Workbook_Open and Auto_Open


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    Re: "The code runs without error but won't display the workbook."

    Instead of the "Sub Button12_Click()" code, what if you use

    Code
    Dim fpath As String
fpath = "C:\Some Folder Name\"
Workbooks.Open fpath & "Pricing Register.xlsm"
or just a single line
Workbooks.Open "C:\Some Folder Name\Pricing Register.xlsm"

    Try opening the workbook manually, then on the View tab, click the Unhide button and see if the workbook is listed there.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login