Using APIs with UserForms

Here are a few things you can do with APIs to make your userforms more functional. All of the code shown will work with Excel 2000 or higher. Note that I am using modeless forms which enable you select ranges and the user interface (menus, other useforms etc)

You might notice that a lot of the code in the samples is the same. I have commented the parts that the code perform it's unique purpose.

Aplogies if I have posted some of the code before, this is just to put all the samples together.

Add a Minimize and Maximize Button
Place this code in a standard module.

Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" _
(ByVal hWnd As Long, ByVal nIndex As Long) As Long

Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" _
(ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

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

Declare Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long

Declare Function SetFocus Lib "user32" (ByVal hWnd As Long) As Long

Public Const WS_MAXIMIZEBOX As Long = &H10000 'This refers to the the maximize button property
Public Const WS_MINIMIZEBOX As Long = &H20000 'This refers to the the minimize button property
Public Const GWL_STYLE As Long = (-16)
Public Const SW_SHOW As Long = 5

Sub ShowForm()
    UserForm1.Show vbModeless
End Sub

Place this code in the userform.

Private Sub UserForm_Activate()
    Dim lStyle As Long
    Dim hWnd As Long

    hWnd = FindWindow("ThunderDFrame", Me.Caption)

    lStyle = GetWindowLong(hWnd, GWL_STYLE)
    lStyle = lStyle Or WS_MAXIMIZEBOX 'This adds the maximize button
    lStyle = lStyle Or WS_MINIMIZEBOX 'This adds the miniimize button

    SetWindowLong hWnd, GWL_STYLE, lStyle

    ShowWindow hWnd, SW_SHOW
    DrawMenuBar hWnd
    SetFocus hWnd

End Sub

Make a Form Resizeable (Drag the edges to resize)
Place this code in a standard module.

Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" _
(ByVal hWnd As Long, ByVal nIndex As Long) As Long

Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" _
(ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

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

Declare Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long

Declare Function SetFocus Lib "user32" (ByVal hWnd As Long) As Long

Public Const WS_THICKFRAME As Long = &H40000 'This refers to the the form resize property

Public Const GWL_STYLE As Long = (-16)
Public Const SW_SHOW As Long = 5

Sub ShowForm()
    UserForm1.Show vbModeless
End Sub

Place this code in the userform.

Private Sub UserForm_Activate()
    Dim lStyle As Long
    Dim hWnd As Long

    hWnd = FindWindow("ThunderDFrame", Me.Caption)

    lStyle = GetWindowLong(hWnd, GWL_STYLE)
    lStyle = lStyle Or WS_THICKFRAME 'This allows you to resize the form

    SetWindowLong hWnd, GWL_STYLE, lStyle

    ShowWindow hWnd, SW_SHOW
    DrawMenuBar hWnd
    SetFocus hWnd

End Sub

Remove the Title Bar
Place this code in a standard module.

Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" _
(ByVal hWnd As Long, ByVal nIndex As Long) As Long

Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" _
(ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

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

Declare Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long

Declare Function SetFocus Lib "user32" (ByVal hWnd As Long) As Long

Public Const WS_CAPTION As Long = &HC00000 'This refers to the title bar
Public Const GWL_STYLE As Long = (-16)
Public Const SW_SHOW As Long = 5

Sub ShowForm()
    UserForm1.Show vbModeless
End Sub

Place this code in the userform.

Private Sub UserForm_Activate()
    Dim lStyle As Long
    Dim hWnd As Long

    hWnd = FindWindow("ThunderDFrame", Me.Caption)

    lStyle = GetWindowLong(hWnd, GWL_STYLE)
    lStyle = lStyle And Not WS_CAPTION 'This removes the title bar

    SetWindowLong hWnd, GWL_STYLE, lStyle

    ShowWindow hWnd, SW_SHOW
    DrawMenuBar hWnd
    SetFocus hWnd

End Sub

Remove the Close (X) Button (Note FindWindowA is added)
Place this code in a standard module.

Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" _
(ByVal hWnd As Long, ByVal nIndex As Long) As Long

Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" _
(ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

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

Declare Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long

Declare Function SetFocus Lib "user32" (ByVal hWnd As Long) As Long

Declare Function FindWindowA Lib "user32" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long 'Note this line exists only in this sample code

Public Const WS_SYSMENU = &H80000 'This refers to the close button
Public Const GWL_STYLE As Long = (-16)
Public Const SW_SHOW As Long = 5

Sub ShowForm()
    UserForm1.Show vbModeless
End Sub

Place this code in the userform.

Private Sub UserForm_Activate()
    Dim lStyle As Long
    Dim hWnd As Long

    hWnd = FindWindow("ThunderDFrame", Me.Caption)

    lStyle = GetWindowLong(hWnd, GWL_STYLE)
    lStyle = lStyle And Not WS_SYSMENU 'This removes the close button

    SetWindowLong hWnd, GWL_STYLE, lStyle

    ShowWindow hWnd, SW_SHOW
    DrawMenuBar hWnd
    SetFocus hWnd

End Sub

Use a "Toolbar" Style Title Bar (Note GWL_EXSTYLE is used, not GWL_STYLE)
Place this code in a standard module.

Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" _
(ByVal hWnd As Long, ByVal nIndex As Long) As Long

Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" _
(ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

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

Declare Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long

Declare Function SetFocus Lib "user32" (ByVal hWnd As Long) As Long

Public Const WS_EX_TOOLWINDOW As Long = &H80 'This refers to the "toolbar" style
Public Const GWL_EXSTYLE As Long = (-20)
Public Const SW_SHOW As Long = 5

Sub ShowForm()
    UserForm1.Show vbModeless
End Sub

Place this code in the userform.

Private Sub UserForm_Activate()
    Dim lStyle As Long
    Dim hWnd As Long

    hWnd = FindWindow("ThunderDFrame", Me.Caption)

    lStyle = GetWindowLong(hWnd, GWL_EXSTYLE)
    lStyle = lStyle Or WS_EX_TOOLWINDOW 'This changes the title bar to the "toolbar" style
    SetWindowLong hWnd, GWL_EXSTYLE, lStyle

    ShowWindow hWnd, SW_SHOW
    DrawMenuBar hWnd
    SetFocus hWnd

End Sub

Have fun!