Hide Workbook Until Fully Loaded

[Logit]

.
Greetings :

Why does this macro not fully hide the workbook when loading ? The MS site and others all say it will .... but there is a hesitation at first, then the workbook is invisible.

Please Login or Register  to view this content.

Is there another method of fully hiding the workbook while loading until I want it visible ?

[Winon]

Not you A G A I N ,

Try this in Thisworkbook Code,

Please Login or Register  to view this content.

Code for the UserForm;

Please Login or Register  to view this content.

Sample attached,

Regards

[Logit]

.
Hey Winon ! Thanks for the reply.

I'm still getting a momentary showing of the workbook before it goes invisible and the UserForm appears. Sorry.

Wish I knew why it does that on my machine / Excel 2007.

Can you think of any other way to hide the workbook until it is fully loaded ?

[Winon]

Hi Logit,

I see what you mean. Unfortunately I don't think what you want is possible, or at least, as far as I know. The way I see it, is that the Application has to be invoked in which the code resides to run its magic, hence the momentarily flicker. The only other way would be to actually open Excel with all but one Sheet hidden, and then with closing the Userform, show the remainder hidden Sheets.

Sorry Buddy,

It is one of those built-in pains of programming which gets incorporated free of charge!

A I am sure you will understand the limitation/s of your request, otherwise the Code will have no idea where to do what.

Kind Regards.

[Logit]

.
Let me describe this some more.

"momentary showing of the workbook" = The workbook (outline shell with the menu bar showing up top, without the worksheets showing) appears for approx 1/2 second. Then a blank white worksheet shows up in the workbook (no grid lines) for just a moment, then the workbook goes invisible. Nothing is on screen at all. The appearance of the workbook/sheet is more than a flicker. You can view the workbook outline with the menu bar up top.

I tried using the TOP and LEFT settings ... and also the HEIGHT and WIDTH settings in a macro. Excel ignores the dynamic settings completely.

I can move the workbook on screen to a specific location / close it and reopen ... the workbook appears where it was last located. But trying to set the location and size within a macro does nothing.

Has anyone else experienced this ? Did you find a solution ?

[xlnitwit]

Perhaps save the workbook in a hidden state and only unhide it in code when necessary.

[Logit]

.
xlnitwit :

Thank you for responding.

Managed to use an API call to hide the workbook, referring to this resource :

https://stackoverflow.com/questions/...cally-in-excel

Hiding works great.


Still seeing that momentary presence of the workbook at the very beginning though. I've watched several YouTube videos where others have used the "Application.Visible = False" to successfully prevent the workbook from appearing until the code directs such .... the code here doesn't respond the same.

Aggravating ...

[xlnitwit]

Simply hiding the workbook through the UI was what I meant (like the personal macro workbook). If you save it in that state, it shouldn’t be visible at all when you open it.

[Logit]

.
My mind must be tired. Not certain I understand what you are referring to.

[xlnitwit]

On the view tab, choose window - hide. The workbook is then hidden. If you save it in that state, it will not be visible when you open it.

[Zer0Cool]

The reason this happens is due to your code being triggered by the workbook_open event, which coincidentally requires the file to open enough for it to run. So if you think about it, there is a period between opening it and the code running in which the file is loading. I would imagine this would be pronounced in larger workbooks that take longer to load.

Why is it you want to hide the window? Does the macro need to display anything to the user at all (form, input box, etc)?

As mentioned, saving it as a hidden workbook would prevent it from being visible (like your personal .xlsb) at all.

Depending on the purpose of your macro(s) overall, you may also consider saving the file as an add-in. It would then not be visible but still be used similar to a hidden workbook, with the behavior being similar to a workbook in your XLSTART folder. The addin once loaded would load ever time you open Excel, unless you disable it.

[Logit]

.
Ok .. gotcha. However, that only hides the worksheet. I want to hide the entire workbook ... hide EXCEL itself when it loads. Then have it visible (application.visible = true) when
the workbook has fully loaded everything and is ready for interaction.

[Logit]

.
Hello Zer0Cool

The workbook goes through several steps when loading. I've minimized the "flickering" of the windows as much as possible but there is still some visible changes until it fully loads.

I do understand that the workbook needs to load before anything else can occur - that does make sense. Not having to see it at all until it is fully loaded would be a nice touch visually.
Maybe by using the API call to hide the workbook in conjunction with a splash screen to cover the loading workbook until it goes 'hidden' ....

Guess I'm being too picky - I've done everything I can think of to minimize the visual transitions except for implementing a splash screen.

Thoughts ?

[Zer0Cool]

I feel like a year or 2 back I wanted to do something similar and found the best I could do was open the file minimized. I think there was a windowstate property of application that allows you to open minimized.

Alternatively, for testing purposes, have you tried using a macro that first sets application.visible = false in a separate file that openes the file you want hidden for you?

I am wondering if its possible to have the result you want if the application.visible property is set false prior to opening your file instead of as part of the open event. You might then get creative with how you open your file. Hypothetically an add-in to open the file hidden (add-in has code to set application visible and then open the file) or a vbscript that sets the property then opens the file, etc.

The other side of it, is how you decide when to show it? Static amount of time, maybe off an event, some way of monitoring when its actually done loading, etc.

[Logit]

.

Alternatively, for testing purposes, have you tried using a macro that first sets application.visible = false in a separate file that openes the file you want hidden for you?

Yes. Still get the momentary visual.

I am wondering if its possible to have the result you want if the application.visible property is set false prior to opening your file instead of as part of the open event.

Yes Still get the momentary visual.

Hypothetically an add-in to open the file hidden (add-in has code to set application visible and then open the file) or a vbscript that sets the property then opens the file, etc.

Not certain how to do either one.

However, in order to initiate the Add-In it would be necessary to have a "blank" workbook to open from which the Add-In can be enabled. That gets me back to the momentary visual of the initial workbook
opening. Do I understand that correctly ?

[xlnitwit]

.
Ok .. gotcha. However, that only hides the worksheet. I want to hide the entire workbook ... hide EXCEL itself when it loads.

Perhaps it is a terminology issue, but what I suggested will hide the entire workbook, not just a worksheet. Hiding the Excel Application at startup is problematic as it has to load, at least partly, before your workbook loads and can run any code. You might use a VBScript file to start an invisible instance of Excel and then load your workbook before making the application visible, rather than having your workbook attempt it- which is too late in the process for what you want.

[Logit]

.
I wouldn't know where to begin with a VBScript. Do you ?

[Logit]

.
I spoke too soon. However, I'm experiencing an issue with the VBScript :

Please Login or Register  to view this content.

I'm receiving this error :

VBS Error.jpg

The VBScript runs Excel without showing the workbook - which is the goal. However, I'm receiving the error message.

I tried eliminating different parts of the second line but still receive the error. Thoughts ?

[Zer0Cool]

It likely has to do with the spaces in your path, maybe an extra set of quotes needed or something.

See my code here:

https://www.excelforum.com/excel-pro...ml#post4837383

Looks like I did "'" to account for spaces in path.

EDIT: I was wrong!!!

Your code is fine, but you cant call the Workbook_open code that way. If you use a VB script to open the workbook, put the code in a standard module and then have the VBscript run the macro.

Alternatively, if the code is in the workbook open, all the script needs to do is open the workbook, the macro will run itself

[Zer0Cool]

Also see this:

https://stackoverflow.com/questions/...-window-hidden

You may be able to have the script open the window hidden so that your VBA isnt responsible for doing it. Again the order is the issue: Workbook open>Loading>VBA can be run. In this fashion if you are trying to hide something via VBA, it always has to wait for Excel to open to then hide it. However your VBS can likely open Excel hidden from the start, allow it to load your file, then show it as you designate.

[Logit]

.
I jumped the gun marking this thread solved before posting the following. Sorry.

I have the error message worked out. Here is the VBScript change for that :

Please Login or Register  to view this content.

Now ... my project requires the MENU BAR to be hidden. Running Excel normally (without VBScript) and hiding the menu bar, there remains a border fully around the workbook display. This is good.
Running the VBScript to start EXCEl, the border is only seen on the right and bottom edges. No border on the left and top.

Need to still hide the menu bar but have the border remain all around.

Here is the ThisWorkbook code :

Please Login or Register  to view this content.

See attached .....


EDIT : Should I start a new thread for the border issue ?

[Zer0Cool]

This may be what you want, your using an old method of hiding the ribbon.

https://stackoverflow.com/questions/...-in-excel-2013

Also can you describe how you are making the window visible again. From your last post, I presume the VBS opens it hidden. Your macro appears to then trigger open, hide the ribbon, hide the status bar, show a form, run a macro, hide the form. I do not see any place in your code that makes the Excel window visible again. Did you not include that part of the code or are you stepping through the macro somehow while evaluating it? In other words how are you seeing this visual glitch, is it on the Excel window itself or something else like the userform?

In any case try the alternative to hiding the ribbon.

[Logit]

Zer0Cool

I am presuming you have run the attached example and not seeing the missing border on the Left and Top sides ? The border is missing here. ????

Regarding how the workbook is eventually made visible ... I apologize for not including the VBScript as well. It is included here (the code). The forum attachment feature will not allow the actual Script. You will of course have to make
an adjustment to the VBScript concerning the path/location of the workbook.

Please Login or Register  to view this content.

[xlnitwit]

It looks OK to me in 2013. You do have a compile error in the file you posted though.

[Logit]

.
No compile error.

I am guessing the difference is Excel 2013. I'm running 2007.

Also tried all the suggestions in : https://stackoverflow.com/questions/...-in-excel-2013

Those commands must be specific to Excel 2013 ... or at least they do not function in 2007. Good thing though ... the link above made me aware that 2013 has a "hide the ribbon" built in. I've been thinking about upgrading
to 2013 .... that's another reason to do so.

[xlnitwit]

The compile issue is in the #If VBA7 part so you wouldn't run into it on 2007.

[Logit]

.
K

Everything I've read in the past 30 minutes indicates the "MinimizeRibbon" is for 2010 - 2013 only. Guess I'll need to upgrade unless anyone has another solution.

I appreciate all the input so far !

[xlnitwit]

I believe I may have a disc for Office 2007 lying around somewhere, so I will try and do some testing tomorrow- time permitting.

[Logit]

.
xlnitwit

Thank so much !

[Logit]

.
Here are two images to demonstrate what I see here :

Loading with the VBScript resulting in NO BORDER on left and top sides :

No Borders.jpg



Loading without the VBScript resulting in a border along the top side :

With Border.jpg


The importance of having a border is so the user can move the window where desired and to be able to resize if needed.

[Zer0Cool]

Regarding how the workbook is eventually made visible ...

I still dont see where you set it to show again. We via VBS tell it to open and NOT be visible, but at what point do we tell it to be visible?

The screenshot also helps see the difference, so you are actually missing the titlebar.

[Logit]

.
application.visible = true doesn't have any effect.

[Zer0Cool]

I think we are losing each other here.

As I recall it, you want to open this excel file and hide the entire Excel window while the file loads, then after it loads show the Excel window/file but not the Ribbon. Is that correct or do you no longer need to open Excel hidden and now only need to hide the ribbon?

I am confused, because if I am correct in your 2 goals above, I do not see any line of code to make the Window visible again, so either it opens visible or it opens hidden and is made visible but I do not see how.

The whole point of the VB script was to open Excel hidden, if thats no longer a need, then the script serves no purpose any longer.

[Logit]

.
Still need to open Excel hidden. The VBScript does that beautifully.

Still need to have the Menu Bar up top not visible. This line in the Workbook_Open does that beautifully :

Please Login or Register  to view this content.

I was calling it the Excel border .. you referred to it as the Title Bar. I'll go with Title Bar from this point forward.

If the Excel is loaded without the VBScript, the Title Bar is visible.

If the Excel is loaded WITH the VBScript, the Title Bar is missing.


I need to have Excel load with the VBScript (because nothing shows until it is fully loaded), and need to have the Title Bar visible as well. How can I accomplish that ?

[Zer0Cool]

Ok so we are almost on the same page.

1. the VBS script opens the file hidden.
2. stuff happens
3. The Ribbon is hidden and the title bar should be visible. You know this because you can see it now, despite the VBS opening the whole window hidden. When did the window become visible and how?

[Logit]

.
Opening Excel with or without the VBScript ... I can see everything ... EXCEPT ... if opened with the VBScript the Title Bar is missing.

Need to have the Title Bar visible.

[Zer0Cool]

Why does this macro not fully hide the workbook when loading ? The MS site and others all say it will .... but there is a hesitation at first, then the workbook is invisible.

Is there another method of fully hiding the workbook while loading until I want it visible ?

Ah ok there it is, this is why I have been confused. At the start of this I was under the impression you wanted to hide the entire ("full") workbook upon opening, until it was done loading. The quotes and others like it above led me to this in addition to your code within VBA:

Please Login or Register  to view this content.

The above seems to me (and is exactly what application.visible should do) like you want Excel to be open but for the entire Excel window (title bar, ribbon, workbook, status bar...all of it) to not be seen. In other words its open, its running but you see nothing resembling Excel on screen.

Beyond that I then thought you wanted to, after having given it time to load invisible, make it visible again but with the Ribbon hidden while still seeing the titlebar and status bar.

So is the above correct or is it actually you just want Excel to open right up and be seen BUT without the ribbon ever being visible?

EDIT:

See this youtube video linked here you can skip to about 3:30 in the video when he closes and opens it. In the video he to opens Excel, sees the ribbon for a split second, then it goes away. Is eliminating that delay and having it open without ever seeing the Ribbon the only thing you need?

Also, why are you trying to do this (assuming its just the ribbon thing)? I dont recall if I asked before, if not I should have. The reason I ask is, despite what the guy in the video says, is its super easy to prevent the code from ever running and hiding the ribbon. Holding Shift and opening the file prevents events from triggering. So you may be going through all this effort to try and prevent people from using the ribbon when 5 mins on Google and some will power will allow them to circumvent this.

[Logit]

.
Yes, I need to have the Menu Bar not visible. In the YouTube video he is using the same command as I :

Please Login or Register  to view this content.

That command needs to stay in my project.

If you will view the video again, he clicks on an icon to initiate the loading process of Excel. For a momentary split second, the Workbook is viewed, with the menu bar showing and the worksheet area/window blank (it's blue in color, no white, no grid lines). Then it disappears as does the menu bar and the Worksheet is visible (white color and grid lines) and there is a Title Bar at the top.

I am attempting to remove from the loading process that split second of momentary flash of Excel showing the menu bar and no worksheet. Hence the use of the VBScript. The VBScript prevents EVERYTHING from being seen during the loading process
until Excel is fully loaded and ready to be used. Then it appears in view.

EXCEPT, using the VBScript prevents the Title Bar from showing after Excel loads.

I need the Title Bar as well.

Regarding a user holding down the SHIFT key while loading .. or using SHIFT / F11 ... or using SHIFT / PAUSE BREAK ... or any other key combo .... my final project will have all of those disabled as well as all code will be locked down, inaccessible, obfuscated. If someone wants to get to the code or change the loading process they will need to use a HEX EDITOR or some other means of hacking the project.

[Winon]

Hi Logit,

Thank you for the Rep.

How about trying it without a Userform? Zbor wrote a beautiful piece of Code some time ago and by tweaking it a bit, I thought that just maybe you would be interested in looking at the attached sample Workbook. You could then play around with the visibility of Sheet1 as you deem fit.

In General Declarations and ThisWorkbook Code;

Please Login or Register  to view this content.

Code for Sheet1;

Please Login or Register  to view this content.

What do you think?

Regards.

[Logit]

.
Thank you Winon for the assist. I've played around with the code but can't get it to do what I want.

I also searched the Net for examples to move the loading process "off screen" then bring the workbook back "on screen". 'hWind' (window handle) seems to be the target but everything I've located is either designed for 64 bit or simply
won't work with 32 bit Excel 2007. At least not on my system.

Me thinks this is getting waaaaay too involved. If I can't get it to do what I want here (easily) what will happen when the user tries to run it on their system ?

I tried some code that loads Excel in the most minimized state possible, which is then covered by a UserForm (which can be a splash screen). I'm going to experiment with the Height and Width settings to see if I can get the workbook
to expand to the correct dimensions once the loading process is complete. I'm thinking it's either this approach or I'll have to live with the momentary flash of a 'half loaded Excel'.

[Logit]

.
Thanks to everyone who provided insight and suggestions !

I'll set this thread to rest.