close/minimise form control

I have tried uploading it a few times and I keep getting a database error message.

It is a 3.5meg zip file.

If you paste this code into the form it should add a minimize button.

(this was taken from an example posted on this site)

Option Explicit

'API functions
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 SetWindowPos Lib "User32" _
                                      (ByVal hwnd As Long, _
                                       ByVal hWndInsertAfter As Long, _
                                       ByVal X As Long, _
                                       ByVal y As Long, _
                                       ByVal cx As Long, _
                                       ByVal cy As Long, _
                                       ByVal wFlags As Long) As Long
Private Declare Function FindWindow Lib "User32" _
                                    Alias "FindWindowA" _
                                    (ByVal lpClassName As String, _
                                     ByVal lpWindowName As String) As Long
Private Declare Function GetActiveWindow Lib "user32.dll" _
                                         () As Long
Private Declare Function SendMessage Lib "User32" _
                                     Alias "SendMessageA" _
                                     (ByVal hwnd As Long, _
                                      ByVal wMsg As Long, _
                                      ByVal wParam As Long, _
                                      lParam As Any) As Long
Private Declare Function DrawMenuBar Lib "User32" _
                                     (ByVal hwnd As Long) As Long


'Constants
Private Const SWP_NOMOVE = &H2
Private Const SWP_NOSIZE = &H1
Private Const GWL_EXSTYLE = (-20)
Private Const HWND_TOP = 0
Private Const SWP_NOACTIVATE = &H10
Private Const SWP_HIDEWINDOW = &H80
Private Const SWP_SHOWWINDOW = &H40
Private Const WS_EX_APPWINDOW = &H40000
Private Const GWL_STYLE = (-16)
Private Const WS_MINIMIZEBOX = &H20000
Private Const SWP_FRAMECHANGED = &H20
Private Const WM_SETICON = &H80
Private Const ICON_SMALL = 0&
Private Const ICON_BIG = 1&





Private Sub UserForm_Activate()
    AddMinimiseButton   'Add a Minimize button to Userform
    AppTasklist Me    'Add this userform into the Task bar
End Sub





Private Sub AddMinimiseButton()
'//Add a Minimize button to Userform
    Dim hwnd As Long
    hwnd = GetActiveWindow
    Call SetWindowLong(hwnd, GWL_STYLE, _
                       GetWindowLong(hwnd, GWL_STYLE) Or _
                       WS_MINIMIZEBOX)
    Call SetWindowPos(hwnd, 0, 0, 0, 0, 0, _
                      SWP_FRAMECHANGED Or _
                      SWP_NOMOVE Or _
                      SWP_NOSIZE)
End Sub


Private Sub AppTasklist(myForm)
'Add this userform into the Task bar
    Dim WStyle As Long
    Dim Result As Long
    Dim hwnd As Long

    hwnd = FindWindow(vbNullString, myForm.Caption)
    WStyle = GetWindowLong(hwnd, GWL_EXSTYLE)
    WStyle = WStyle Or WS_EX_APPWINDOW
    Result = SetWindowPos(hwnd, HWND_TOP, 0, 0, 0, 0, _
                          SWP_NOMOVE Or _
                          SWP_NOSIZE Or _
                          SWP_NOACTIVATE Or _
                          SWP_HIDEWINDOW)
    Result = SetWindowLong(hwnd, GWL_EXSTYLE, WStyle)
    Result = SetWindowPos(hwnd, HWND_TOP, 0, 0, 0, 0, _
                          SWP_NOMOVE Or _
                          SWP_NOSIZE Or _
                          SWP_NOACTIVATE Or _
                          SWP_SHOWWINDOW)
End Sub

Thanks leith that worked a treat, wish I had the code earlier.

But back to my original problem

what i want to achieve when the button is pressed but I keep getting a syntax error.

If the form is minimized then restore else open a new form.

code I have so far

Sub ControlPanel()

Dim frm As ControlPanel

If ControlPanel.WindowState = 1

 
Then ControlPanel.WindowState = 0


Else: Set frm = New ControlPanel

frm.Show
End Sub

do you have any ideas?

Thanks Leith That solved the problem.

Hi leith

I was using Excel 2010 when I cread the module for the Min/Max controls using the code you gave me in this thread and it worked fine. I just checked in 2007 and the min button closes the form and in 2003 the controls dont show at all unless I open the form from the VBA editor.

Any Ideas on what the problem could be or how to solve it? The majority of the people using the work book will be uisng excel 2003.

Many thanks

Code below.

Private Sub UserForm_Activate()
  AddToForm MIN_BOX
  AddToForm MAX_BOX
End Sub

Private Const GWL_STYLE As Long = -16
Public Const MIN_BOX As Long = &H20000
Public Const MAX_BOX As Long = &H10000

Const SC_CLOSE As Long = &HF060
Const SC_MAXIMIZE As Long = &HF030
Const SC_MINIMIZE As Long = &HF020
Const SC_RESTORE As Long = &HF120

Private Declare Function GetWindowLong _
  Lib "user32.dll" _
   Alias "GetWindowLongA" _
    (ByVal hwnd As Long, ByVal nIndex As Long) As Long
               
 Private Declare Function SetWindowLong _
  Lib "user32.dll" _
   Alias "SetWindowLongA" _
    (ByVal hwnd As Long, _
     ByVal nIndex As Long, _
     ByVal dwNewLong As Long) As Long
     
'Redraw the Icons on the Window's Title Bar
 Private Declare Function DrawMenuBar _
  Lib "user32.dll" _
   (ByVal hwnd As Long) As Long

'Returns the Window Handle of the Window accepting input
 Private Declare Function GetForegroundWindow _
  Lib "user32.dll" () As Long

Public Sub AddToForm(ByVal Box_Type As Long)

 Dim BitMask As Long
 Dim Window_Handle As Long
 Dim WindowStyle As Long
 Dim Ret As Long

   If Box_Type = MIN_BOX Or Box_Type = MAX_BOX Then
      Window_Handle = GetForegroundWindow()
  
       WindowStyle = GetWindowLong(Window_Handle, GWL_STYLE)
       BitMask = WindowStyle Or Box_Type
  
      Ret = SetWindowLong(Window_Handle, GWL_STYLE, BitMask)
      Ret = DrawMenuBar(Window_Handle)
   End If

End Sub

Thanks Leith.

I have had a few compatibilty problems when doing stuff in excell 2010.

Back to 2003 me thinks.