maximizing the size of a UserForm

[Takashi Yamauchi]

Could anyone give me a suggestion?

As I open a UserForm, I want to maximize the size of the form (making it as
large as my monitor). In the VB6 Form property, there is a property called
"WindowState" and I can select "Maximized." But, I can't find a similar
property in Excel VBA. How can I automatically maximize a UserForm in Excel
VBA?

Thank you

Takashi Yamauchi

 

[JLGWhiz]

The VBA method for resizing the UserForm is Move Method(Forms). The help
file does not give a lot of information on it, but width and height are
arguments to the move command for the UF.

 

[JLGWhiz]

I just created a UserForm for a test. I put the width and height dimensions
in the initialize event like this:

Private Sub UserForm_Initialize()
With UserForm1
.Width = 640
.Height = 480
End With
End Sub

Then in the VBA module I use:

Sub UFtst()
UserForm1.Show(Modal)
MsgBox "Check Size"
Unload UserForm1
End Sub

It brings the form up in the larger dimension, which indicates that you can
set it to
fit your full screen size.

 

[Takashi Yamauchi]

Thank you.

But I guess, this doesn't work, because, in your code, you have to know and
specify the width and height of UserForm1 beforehand. But I want to maximize
UserForm1 as you run the program and irrespective of the size of the display
monitor you are using (e.g., desktop, laptop, etc.). So, for example, you
wrote an Excel VBA, and give it to your friend. He opens the program. When
he opens it, the UserForm1 should be maximized no matter what display
monitor he has. But unfortunately, the code you gave me doesn't do that, I
guess. So, I guess, what is needed is a procedure that measures the
dimensions of the monitor, and feeds the information to UserForm1???

e.g.,
Userform1.width = DisplayMonitor.width
Userform1.height = DisplayMonitor.height
Userform1.left = 0
Userform1.top =0...

something like that...

But thank you anayway.


takashi yamauchi

 

[JLGWhiz]

I have seen code that basically fits to screen, but I do not believe it would
apply to the UserForm. Maybe somebody smarter than me knows how to write a
function that would do that by getting the screen size and setting it to a
variable that could be used like maximize. That is about the only solution I
can think of right now. I am no good at functions or I would try it for you.

 

[JLGWhiz]

Here it is:

Private Sub UserForm_Initialize()
With UserForm1
.Width = Application.UsableWidth
.Height = Application.UsableHeight
End With
End Sub

I knew I had seen it somewhere and after a lot of stumbling around, I found
it.
This in the Initialize event for the userform will leave just a small space
around the form. This is as close as you will get to maximize.

 

[Nigel]

Try putting this into your UserForm initialize event.......

Application.WindowState = xlMaximized
Me.Width = Application.Width
Me.Height = Application.Height

 

[Takashi Yamauchi]

Thanks. This is great!!

''''''''''''''*****************
'maximize the size of the window under the application.
Application.WindowState = xlMaximized

'maximize the size of UserForm1
With UserForm1
.Width = Application.UsableWidth
.Height = Application.UsableHeight * 1.2
End With

''''''''''''''*****************

So, if the application (Excel) is maximized, then UserForm1 is also
maximized (and cover pretty much the entire monitor).
Now, I'm trying to find a procedure that maximizes the size of Application
(not the windows in the Application). If I can find that, this will pretty
much take care of what I need.....

Thank you.


Takashi Yamauchi

 

[Takashi Yamauchi]

It looks like

Application.WindowState = xlMaximized

will maximize the Application


ty

 

[Takashi Yamauchi]

thanks. this one worked.


ty

Try putting this into your UserForm initialize event.......

Application.WindowState = xlMaximized
Me.Width = Application.Width
Me.Height = Application.Height

--

Regards,
Nigel
(e-mail address removed)

 

[Rick Rothstein \(MVP - VB\)]

As I open a UserForm, I want to maximize the size of the form

(making it as large as my monitor).

If you don't mind that your user can move the form around after it is
maximized, then maybe you can use this code solution.

The following is pieced together from a lot of different API sources I found
on Google... I am confident that I have everything correct and that this
will work correctly in your application (as with any test code provided in a
newsgroup response, you should try this out on a copy of your workbook
first). What the code does is make your UserForm fill the entire screen
(which is what I assume you meant when you said "making it as large as my
monitor"). Put all the code below my signature into a Standard Module (click
Insert/Module from the VBA editor's menu, copy/paste the code below my
signature into the window that appears). Then, to maximize the form, execute
this statement...

MaximizeUserForm Me.Caption

from anywhere within your UserForm's code (since that is where Me exist at;
use the actual UserForm's name in place of Me for code outside of the
UserForm) WITH THE EXCEPTION of the UserForm's Initialize event (it will
produce a "Form already displayed; can't show modally" error if you try it
there). For your indicated usage, you will probably want to execute the
above statement from the UserForm's Activate event.

Rick


'******** Place all of the following into a Standard Module ********
Private Declare Function GetDesktopWindow Lib "user32" () As Long

Private Declare Function FindWindowEx Lib "user32" _
Alias "FindWindowExA" _
(ByVal hWnd1 As Long, _
ByVal hWnd2 As Long, _
ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long

Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long

Private Declare Function GetWindowThreadProcessId Lib "user32" _
(ByVal hWnd As Long, _
ByRef lpdwProcessId As Long) As Long

Private Declare Function ShowWindow Lib "user32" _
(ByVal hWnd As Long, _
ByVal nCmdShow As Long) As Long

Private Const SW_MAXIMIZE = 3

Private Function FindOurWindow(Optional sClass As String = vbNullString, _
Optional sCaption As String = vbNullString)
Dim hWndDesktop As Long
Dim hWnd As Long
Dim hProcThis As Long
Dim hProcWindow As Long
hProcThis = GetCurrentProcessId
hWndDesktop = GetDesktopWindow
Do
hWnd = FindWindowEx(hWndDesktop, hWnd, sClass, sCaption)
GetWindowThreadProcessId hWnd, hProcWindow
Loop Until hProcWindow = hProcThis Or hWnd = 0
FindOurWindow = hWnd
End Function

Public Sub MaximizeUserForm(UFcaption As String)
Dim UF_hWnd As Long
UF_hWnd = FindOurWindow(, UFcaption)
ShowWindow UF_hWnd, SW_MAXIMIZE
End Sub

 

[Rick Rothstein \(MVP - VB\)]

As a point of information, it doesn't fill the entire screen on my Vista
system (assuming that makes a difference)... it fills maybe 95% of it.

Rick