Make Userform go Fullscreen

[Gregs]

Hi Mr Excel,

Just want to know how you make a Userform go full screen when the spreadsheet is opened. At the moment, when I open the spreadsheet the userform is Autpen but I want it to be fullscreen or if I click on a button to generate it I also may want full screen.

Any help would be much appreciated.

Gregs

 

[Dragracer]

Gregs, here a bit of code to do what ask but the one problem with changing the size of a userform is it does not move the position of the items in the userform. They will all retain their positions relitive to the top and left edges. You will have to write the code to move all the items. I would suggest using the Userform Resize event.

Anyway give this a try. Put it in your userform code module.

<pre>
Dim lAppWindowState As Long

Private Sub UserForm_Initialize()
With Application
lAppWindowState = .WindowState
.WindowState = xlMaximized
Me.Move 0, 0, .Width, .Height
End With
End Sub

Private Sub UserForm_Terminate()
Application.WindowState = lAppWindowState
End Sub


</pre>

 

[Gregs]

Thanks, it works but like you said the buttons on the Userform don't move. So therefore, what is the code to make them change ?? Any help would be appreciated.

Thanks
Gregs

 

[Gates Is Antichrist]

Unlike Access this has not been convenient, unless it recently changed. (Always indicate your Excel version when asking questions.)

The API solution is
Private Const SW_MAXIMIZE = 3
ShowWindow hWndForm, SW_MAXIMIZE

You can investigate this further at http://www.bmsltd.co.uk/Excel/Default.htm

However if you don't want to use API calls (and for something this simple who could blame you), do this (stolen from Chip Pearson):

Private Sub UserForm_Activate()
With Application
Me.Move .Left, .Top, .Width, .Height
End With
End Sub

 

[Gates Is Antichrist]

I just saw the prior reply. His answer is more robust. As to the args of 0,0, that is valid for full screen, which is what you specifically asked for.

On the followup, I haven't used Resize there, so I have nothing to add.

 

[Dragracer]

I too was going to suggest you take a look at Stephen Bullens site. Check out the FormFun.zip and the Resizer.zip

Good luck

 

[Gregs]

All I need to know is how to resize the command buttons on the UserForm and I'm done...thats it. Can anyone help here

What is the link to Stephen Billens Website?

Thanks
Gregs

 

[stevebausch]

At least three topics here:

When you say Userform, you mean Userform, and not Worksheet, correct?

I will work on that assumption.

A userform can be sized using the .Zoom method. You will need to come up with a relationship between the points width of your intended display resolution.


.Zoom = CInt(Application.Width / 774 * 90)
.Width = Application.Width
.Height = Application.Height

Is what I use to 'full screen' a userform of my current project.

Actually, it just matches the dimensions of Excel's window, at that point in time. So, if it is maximized, the form will be maximized. Whatever the 'footprint' of Excel, the userform will match it.

774 is the approximate point value of 1024 by 768 display resolution. 90 is not a scientific number, it just so happens that if the points returned are 774 (usually is), and 774 is divided by 774 (usually is), then a zoom of 90 works out pretty good.

And if the display resolution is changed, then the form will resize itself.

Will it look perfect? I doubt it. On a smaller resolution, say 800 by 600, it starts to look grainy. But the whole form is visible.

If this doesn't make sense, just ask.

Steve
This message was edited by stevebausch on 2002-09-22 19:42

 

[Gregs]

Steve,

I currently have the Userform full screen with the taskbar showing using code Dragacer gave me below. However, I have some command buttons on the Userform that are NOT resizing when the form goes fullscreen. I need some code to make them resize.

Private Sub UserForm_Initialize()

Dim lAppWindowState As Long

With Application
lAppWindowState = .WindowState
.WindowState = xlMaximized
Me.Move 0, 0, .Width, .Height
End With

End Sub


Any help would be greatly appreciated..

 

[Dragracer]

Gregs, Stephen Bullens site is the one that Gates suggested.

Steve's idea of the zoom should work depending on how much stuff is on your userform.

What you could do is in the Userform_Resize sub is set the commandbuttons height and width using the ratio of height and width of to the userform based on your original design design. Check the width of you buttons and userform in your design to determine the ratio then try a code like this.

<pre>
Private Sub UserForm_Resize()
i = UserForm1.Width
CommandButton1.Width = i / 5
End Sub

</pre>

Hope this helps