Solved: Windowstate = xlnormal then Center Position?

[SherryO]

Is it possible to have a windowstate of xlnormal and then have it auto center in the middle of the screen? I am trying to hide workbooks opening in the background behind a userform that is showing the progress of the macro. I don't want my users to see the workbooks opening. I have them set as follows which works very well if the screen resolution is set to 1280x1024, but does not work on other settings. Any help would be greatly appreciated. Thanks!!
Workbooks.Open FileName:="c:\OEM\All.xls", ReadOnly:=True
With ActiveWindow
.WindowState = xlNormal
.Top = 250
.Left = 400
.Height = 25
.Width = 25
End With

[mdmackillop]

You can maybe use the screen resolution size to set your values
Have a look at Johnske's KB item (http://vbaexpress.com/kb/getarticle.php?kb_id=337)

[Bob Phillips]

Why not just make the window non-visible whilst the form is active?

[SherryO]

And how would one do that? I never even thought of it. Thank you.

[SherryO]

Thank you. I am such a twit! I can't believe I didn't think of that. Talk about overengineering something! BTW The suggested KB entry worked like a champ too!

[Bob Phillips]

Well, you would need to collect the names of all hidden windows, to restore them later.

One way would be to add it to a collection



Dim collWindows As Collection
Set collWindows = New Collection


Then all you do is



Workbooks.Open Filename:="c:\OEM\All.xls", ReadOnly:=True
With ActiveWindow
.Visible = False
collWindows.Add .Caption, .Caption
End With


Then restore them all later with



Dim sWindow
For Each sWindow In collWindows
Windows(sWindow).Visible = True
collWindows.Remove 1
Next sWindow

[SherryO]

Does hiding the workbooks suspend calculations? I believe it's suspending the open eventm but I could be wrong. Thanks again.

[Bob Phillips]

No, that shouldn't be a problem. But you might get problems if you have any actions that require a visible window.