Archive for November, 2010

Although Excel UserForms do not inherently support minimize/maximize functionality, I got a request stating that the application users needed to be able to open multiple Userforms simultaneously, minimizing, maximizing and switching between. Or the world will end. Make it happen.

No need to sweat if you get marching orders like this. Armed with the information in this post you’ll have ’em minimizing and maximizing to their heart’s content.

As always, let’s start from the beginning:

* Create a new macro-enabled Excel workbook.

* Switch to the VBA editor and add a new code module. Name it modAPI. Enter the following code in the Declarations area. These are all the required Windows API function declarations and constants that will be required to allow standard window minimize/maximize behavior in our UserForms:

Public Const WS_MINIMIZEBOX = &H10000
Public Const WS_MAXIMIZEBOX = &H20000
Public Const GWL_STYLE = (-16)
Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long 
Public Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long
Public Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Public Declare Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long

* Next, add the following function to the modAPI module:

Public Sub AddMinMaxButtons(ByVal FormCaption As String, ByVal MinButton As Boolean, ByVal MaxButton As Boolean)
'Add either minimize button, maximize button, or both buttons to the UserForm with the specified caption.
'IMPORTANT: If a UserForm's caption is changed dynamically in your program, this subroutine must be run again (with the new Caption) or the min/max buttons will disappear.
Dim hWnd As Long
Dim lngStyle As Long
hWnd = FindWindow(vbNullString, FormCaption)
lngStyle = GetWindowLong(hWnd, GWL_STYLE)
If MaxButton Then
    lngStyle = lngStyle Or WS_MAXIMIZEBOX
End If
If MinButton Then
    lngStyle = lngStyle Or WS_MINIMIZEBOX
End If
SetWindowLong hWnd, GWL_STYLE, lngStyle
DrawMenuBar hWnd
End Sub

* Create a new UserForm. Change the Caption property to “My Test Form”

* In the UserForm_Activate event of the new form, enter the following code:

Private Sub UserForm_Activate()
'Set the min/max button setting for the form.
 AddMinMaxButtons Me.Caption, MinButton:=True, MaxButton:=True
End Sub

* Next, add the following code to the Workbook_Open() event of the ThisWorkbook module:

Private Sub Workbook_Open()
    UserForm1.Show vbModeless
End Sub

At this point you can save the workbook, close it, then open it again (or just run the project from the Workbook_Open() event in the ThisWorkbook object). You’ll see that the UserForm opens, it has proper Minimize and Maximize Buttons and the underlying worksheets are still accessible.

It’s that easy.

Finally, to demonstrate that Min/Max buttons will disappear if the UserForm’s caption changes, open the UserForm and add a Command Button. Set the Name property to cmdChange and the button’s Caption property to “Change Form Caption”. Open the code behind the button and enter the following code:

Private Sub cmdChange_Click()
'Change the form's caption and observe what happens to the Min/Max buttons.
    Me.Caption = "New Test Caption"
End Sub

UserForm for testing Min/Max Buttons

* Once again, save, close and re-open the workbook. When you see the UserForm, click on the “Change Form Caption” button. Immediately you will see the form’s caption change, and the Min/Max buttons disappear. So always insure that if your form name can change on the fly, make sure you add another call to the AddMinMaxButtons method after the caption changes.



Read Full Post »