Excel Window Size 1

[StevePB]

I am using forms for user input (which run from the Workbook_Open event), which modify the characteristics of a file. I don't want the user to see the file in the spreadsheet (which is an imported text file, and will be written out as a text file after required changes are made).

I have tried minimising the Excel window in the code, using
Application.Windowstate=xlMinimized, but if I put this in the Workbook_Open event, I get a minimized window in the task bar which is flashing, because it can't display the form until it has been maximized. The same thing happens if I put the code in the UserForm_Initialize event.

As a last resort, I set up a shortcut on the desktop to the workbook, to run minimized, however I have found that although this works ok with Excel97 and Windows 98, it doesn't with Excel2000.

I'm sure there must be a good way to do this, that I can't figure. Can anyone help me with this?

 

[uberpudge]

Hello StevePB!

Application.WindowState will set the window state for the whole application (Excel in this case).

Try using this instead:
ActiveWindow.WindowState = xlMinimized

This way only the active window is affected.

If you have mulitple windows open and you want to minimize them all, try using this:

Public Sub MinimizeAllWindows()
Dim wnd As Window

For Each wnd In Application.Windows
wnd.WindowState = xlMinimized
Next

Set wnd = Nothing
End Sub

Hope this helps,
Pete

 

[StevePB]

Thanks for your response Pete - but I do want the application window minimised, so that only the form can be seen, to avoid accidental interaction with Excel. I could set the form size to fill the screen, but I don't know if this can be coded to allow for different screen resolutions?

Thanks again, SB.

 

[uberpudge]

Hello StevePB,

If you want the userform to fill the screen (hiding the userform) try the adding the following in the UserForm code section:

Private Sub UserForm_Activate()
SetUserFormSize
End Sub

Private Sub UserForm_Initialize()
SetUserFormSize
End Sub

Private Sub UserForm_Resize()
SetUserFormSize
End Sub

Public Function SetUserFormSize()
UserForm1.Height = Application.Height
UserForm1.Width = Application.Width
End Function

Hope this helps,
Pete

 

[StevePB]

Pete, thanks, that's a good idea. I'll force the application window to maximize, then match the form size to the application window. I was just looking at it the wrong way round!

Thanks a lot for your help.

Best regards, Steve.