?
Solved

Minimizing and maximizing Excel userform

Posted on 2003-03-09
8
Medium Priority
?
1,267 Views
Last Modified: 2013-12-25
Is there a way you can minimize and maximize Excel userform?
Please advise.

Thank you.
0
Comment
Question by:LostCause
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 

Expert Comment

by:applayer
ID: 8098755
As far as I know you can't min and max an excel userform.  You can work around this most of the time by using the userform's hide and show methods to switch between your forms.  
0
 

Expert Comment

by:applayer
ID: 8098761
You can also change the userform's height and width, and position on the screen if that helps...

You could also try adding mutliple buttons on a toolbar to launch your different forms...and then either hide or unload them when you want to switch between your forms.
0
 

Expert Comment

by:applayer
ID: 8098771
...so if you need to take up the whole screen you can do that by setting with userform .width, .height, .top, and .left properties.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Accepted Solution

by:
kokoloko earned 1000 total points
ID: 8103086
Yes you can!! It just takes a bit of API magic...

Open a new excel book, open the control toolbox and click on design mode. Place a command button on the sheet, and double click it. Paste the following code:

Private Sub CommandButton1_Click()
  UserForm1.Show   'Just to show our form
End Sub

OK, now add a user form (UserForm1) to the VBAProject and paste the following code:

Private Const WS_MINIMIZEBOX As Long = &H20000    'Style to add a Minimize box on the title bar
Private Const WS_MAXIMIZEBOX As Long = &H10000    'Style to add a Maximize box to the title bar
Private Const SW_SHOW As Long = 5
Private Const GWL_STYLE As Long = (-16)           'The offset of a window's style
   
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long
Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Private Declare Function ShowWindow Lib "user32" (ByVal hWnd As Long, ByVal nCmdShow As Long) As Long
Private Declare Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long
Private Declare Function SetFocus Lib "user32" (ByVal hWnd As Long) As Long


Private Sub UserForm_Activate()
  Dim iStyle As Long
  Dim hWndForm As Long

  'Get the userform's window handle
  If Val(Application.Version) < 9 Then
    hWndForm = FindWindow("ThunderXFrame", Me.Caption)  'XL97
  Else
    hWndForm = FindWindow("ThunderDFrame", Me.Caption)  'XL2000
  End If
 
 
  iStyle = GetWindowLong(hWndForm, GWL_STYLE)
  iStyle = iStyle Or WS_MINIMIZEBOX Or WS_MAXIMIZEBOX
  SetWindowLong hWndForm, GWL_STYLE, iStyle
   
  'Show the window with the changes
  ShowWindow hWndForm, SW_SHOW
  DrawMenuBar hWndForm
  SetFocus hWndForm
End Sub


Now, save the project, close the VBA IDE, exit design mode, and click on the button to show the form....
Voilá, you get an Excel UserForm with maximize and minimize buttons.

0
 

Expert Comment

by:applayer
ID: 8103877
very cool kokoloko!
0
 

Author Comment

by:LostCause
ID: 8154396
Thank you, kokoloko.
0
 
LVL 3

Expert Comment

by:kokoloko
ID: 8154609
you're welcome
0
 

Expert Comment

by:rbitstream
ID: 12514175
kokoloko nice ..

How would you set this to open the form maximized?
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question