Disabling the App-Minimize button in Excel

[GVF]

I have an Excel VBA project that receives information from the serial port and pastes it to a spreadsheet. Everything works fine until someone minimizes Excel, and then the code stops and must be restarted. This leaves a gap in the data.
(The code runs in the background even if another application is active. Minimizing Excel is the problem.)

Is there a way to disable the Application Minimize button with VBA?

 

[PHV]

You may periodically reset the Application.WindowState property in your VBA code.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rmikesmith]

GVH,

The following should do what you want (also disables the Minimize menuitem on the System, or Control menu). Put this in a standard code module.

' Win32 API Constant & Function declarations

Type MENUITEMINFO
  cbSize As Long
  fMask As Long
  fType As Long
  fState As Long
  wID As Long
  hSubMenu As Long
  hbmpChecked As Long
  hbmpUnchecked As Long
  dwItemData As Long
  dwTypeData As String
  cch As Long
End Type

'Menu item constants.
Const SC_MINIMIZE As Long = &HF020&
Const xSC_MINIMIZE As Long = -10

'SetMenuItemInfo fState constants.
Const MFS_GRAYED As Long = &H3&
Const MFS_DEFAULT As Long = &H1000&

'SetMenuItemInfo fMask constants.
Const MIIM_STATE As Long = &H1&
Const MIIM_ID As Long = &H2&

'SendMessage constants.
Const WM_NCACTIVATE  As Long = &H86

'Window constants
Const WS_MINIMIZEBOX As Long = &H20000
Const WS_MAXIMIZEBOX As Long = &H10000
Const GWL_STYLE As Long = (-16)



Declare Function SetMenuItemInfo Lib "user32" Alias
    "SetMenuItemInfoA" (ByVal hMenu As Long, ByVal un As Long, ByVal bool As Boolean, lpcMenuItemInfo As MENUITEMINFO) As Long
Declare Function GetDesktopWindow Lib "user32" () As Long
Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Declare Function GetCurrentProcessId Lib "kernel32" () As Long
Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hwnd As Long, ByRef lpdwProcessId As Long) As Long
Declare Function GetSystemMenu Lib "user32.dll" (ByVal hwnd As Long, ByVal bRevert As Long) As Long
Declare Function SendMessage Lib "user32" Alias 
   "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, 
   ByVal wParam As Long, lParam As Any) 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


Sub DisableAppMinimize()
Dim hWndExcel As Long
Dim hSysMenu As Long
Dim retVal As Long
Dim MI_Info As MENUITEMINFO
    
   hWndExcel = GetWindowHandle("XLMAIN", Application.Caption)
   hSysMenu = GetSystemMenu(hWndExcel, 0)
         
   With MI_Info
     .cbSize = Len(MI_Info)
     .fState = MFS_GRAYED
     .wID = xSC_MINIMIZE
     .fMask = MIIM_ID Or MIIM_STATE
   End With
   retVal = SetMenuItemInfo(hSysMenu, SC_MINIMIZE, False, MI_Info)

   retVal = GetWindowLong(hWndExcel, GWL_STYLE)
   retVal = retVal And Not (WS_MINIMIZEBOX)
   retVal = SetWindowLong(hWndExcel, GWL_STYLE, retVal)
   
   retVal = SendMessage(hWndExcel, WM_NCACTIVATE, True, 0)
   
End Sub


Sub EnableAppMinimize()
Dim hWndExcel As Long
Dim hSysMenu As Long
Dim retVal As Long
Dim MI_Info As MENUITEMINFO

   hWndExcel = GetWindowHandle("XLMAIN", Application.Caption)
   hSysMenu = GetSystemMenu(hWndExcel, 0)
         
   With MI_Info
     .cbSize = Len(MI_Info)
     .fState = MFS_DEFAULT
     .wID = SC_MINIMIZE
     .fMask = MIIM_ID Or MIIM_STATE
   End With
   retVal = SetMenuItemInfo(hSysMenu, xSC_MINIMIZE, False, MI_Info)

   retVal = GetWindowLong(hWndExcel, GWL_STYLE)
   retVal = SetWindowLong(hWndExcel, GWL_STYLE, WS_MINIMIZEBOX Or retVal)
      
   retVal = SendMessage(hWndExcel, WM_NCACTIVATE, True, 0)
      
End Sub


Function GetWindowHandle(Optional ByVal sClass As String = vbNullString, Optional ByVal sCaption As String = vbNullString)
Dim hWndDesktop As Long
Dim hwnd As Long
Dim hProcThisInstance As Long
Dim hProcWindow As Long

   hWndDesktop = GetDesktopWindow
   hProcThisInstance = GetCurrentProcessId
   Do
     hwnd = FindWindowEx(hWndDesktop, hwnd, sClass, sCaption)
     GetWindowThreadProcessId hwnd, hProcWindow
   Loop Until hProcWindow = hProcThisInstance Or hwnd = 0
   GetWindowHandle = hwnd

End Function

Call DisableAppMinimize before you begin collecting data. To restore normal application minimize behavior, call EnableAppMinimize (or simply quit Excel; normal
application window functionality will be restored on next launch).

p.s. The GetWindowHandle function and associated calls to it can be eliminated if you are running Excel 2002 or later as these versions expose Excel's main window handle through the Application.Hwnd property.

p.s.s Be mindfull of line breaks in the lengthy API function declarations and the need to add any line continuation characters (check carefully after pasting into your module).


Regards,
Mike

 

[GVF]

Outstanding Michael, exactly what I had in mind.

Thank you very much.

Greg