Auto_open failure

[Jag108]

HI I am trying to hide the active window in Excel, the code actually runs, but as soon as the last end sub runs the windows pops back again.

Sub Auto_Open()
MsgBox "This ran!"
Add_Workbook_Menu_And_Items
End Sub
Sub my_auto_open()
Dim w As Window
MsgBox "This ran2!"
' For Each w In Workbooks("Star_Plus_Hindi_MKII.xls").Windows
' w.Visible = False
' Next
If ActiveWorkbook.Name = "Star_Plus_Hindi_MKII.xls" Then
With Application
.ActiveWindow.Visible = False 'HIDES THE ACTIVE WINDOW ONLY
End With
Workbooks("Star_Plus_Hindi_MKII.xls").Save
End If
MsgBox "This ran3!"
End Sub



AS you can see I have tried a number of various way to get the window to stay hidden, all are failing.

Help

[lucas]

What do you mean by 'HIDES THE ACTIVE WINDOW ONLY
do you mean sheet? or do you mean you have multiple workbooks open...?

[geekgirlau]

This needs to go in the "ThisWorkbook" module - tested in 2003

[VBA]Private Sub Workbook_Open()
ActiveWindow.Visible = False
End Sub
[/VBA]

[Jag108]

I need to hide the window that has the Auto_open sub in so that the users do not see the reference data.

And yes there will be more than likely multiple workbooks open.

I have tried the ActiveWindow.visible = False as well.

The problem I am having is that as soon as the last Exit Sub has been run the Window reappears "Pop"!!!!

So all of the code that I have posted runs but does not stay as I want it to....

[geekgirlau]

But have you tried running that line of code in the Workbook_Open event rather than Auto_Open? As I said, this worked for me in v2003.

[Norie]

Eh, Auto_Open has been supersuperseded by Workbook_Open.

I don't know if it will solve your problem but I suggest you take geekgirlau's advice.

[Paul_Hossler]

Actually you can still use both -- WB Open fires first, and then Auto_Open. But since WB Open is an event, if events are disabled (by another WB being open or bying turned off), it won't fire, although Auto_Open will

[Norie]

Paul

I know you can still use both.

But they don't act in exactly the same way.

I can't remember the exact details but there are occasions when Auto_Open won't be triggered when a workbook is opened.

I think it's only still supported for backward compatibility, the same as Excel 4 macros are.

[Paul_Hossler]

Norie

Possibly it is only for backward compat, but I do know that there have been many times I've turned off events so that something like Worksheet_Change, etc. won't fire, but that also meant that any desired events wouldn't fire also

I assume that since EnableEvents is Application level, turning it off in one WB would disable it for any currently open or newly opened WB.

[johnske]

While Auto_Open, Auto_Close, Auto_Activate, or Auto_Deactivate are provided mainly for backward compatibility (and also to allow you to write 'event-like' code for earlier excel versions), you can sometimes use the subtle differences between automacros and event procedures to your advantage.

For instance, Auto_open will not run if the workbook is opened by another macro, you must use the RunAutoMacros method to run it, so if you don't want a Workbook_Open event to run when opened by a macro you can use Auto_open instead. On the other hand, Workbook_open will run if the workbook is opened by a macro, you must then always remember to use Application.EnableEvents = False when using code to open that workbook and prevent it running, then put Application.EnableEvents back to true after it's open.

There are other reasons of course - and other quirks may sometimes be used to advantage. For instance Workbook_Open normally runs before Auto_Open EXCEPT when both Workbook_Open and Auto_Open procedures are used in a template - in that case (if i remember right) the order is reversed, Auto_Open runs first and Workbook_Open second, but once the template's saved as an ordinary file it reverts back to 'normal' - so if that's actually what you want to happen...