Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Minimizing and maximizing Excel userform

Posted on 2003-03-09
8
Medium Priority
?
1,456 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
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
Technology Partners: 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

Independent Software Vendors: 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!

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

578 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