Make Userform go Fullscreen

Gregs

Board Regular
Joined
Sep 6, 2002
Messages
96
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 Auto_Open 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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Dragracer

Board Regular
Joined
Jun 9, 2002
Messages
151
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

Board Regular
Joined
Sep 6, 2002
Messages
96
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

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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

Well-known Member
Joined
Aug 15, 2002
Messages
1,961

ADVERTISEMENT

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

Board Regular
Joined
Jun 9, 2002
Messages
151
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

Board Regular
Joined
Sep 6, 2002
Messages
96

ADVERTISEMENT

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

Well-known Member
Joined
May 11, 2002
Messages
810
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

Board Regular
Joined
Sep 6, 2002
Messages
96
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

Board Regular
Joined
Jun 9, 2002
Messages
151
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
 

Forum statistics

Threads
1,144,312
Messages
5,723,653
Members
422,508
Latest member
Lordkit1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top