I am trying to prevent a workbook I made from being minimized/maximized by
the user. I have command buttons to do that and I want to force them to use
the command buttons. The code below disables the min/max application buttons
in Excel 2003, but it only disables the close button in Excel 2007. It also
disables dragging to resize the application window in 2007, but that doesn't
help me. I need to disable the min/max button and it needs to work in Excel
2007. Any suggestions?

Thanks

Steve

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

Declare Function GetSystemMenu Lib "user32" (ByVal hwnd As Long, _
ByVal bRevert As Integer) As Integer

Declare Function DeleteMenu Lib "user32" (ByVal hMenu As Integer, _
ByVal nPosition As Integer, ByVal wFlags As Integer) As Integer

'The following procedure disables the Control menu.
Sub Disable_Control()
Dim X As Integer, hwnd As Long
hwnd = FindWindow("XLMain", Application.Caption)
For X = 1 To 9
'Delete the first menu command and loop until
'all commands are deleted
Call DeleteMenu(GetSystemMenu(hwnd, False), 0, 1024)
Next X
End Sub

'The following procedure restores the Control menu.
'Note that to run this procedure, the Declare statements above
'must be in the module.
Sub RestoreSystemMenu()
Dim hwnd As Long
'Get the window handle of the Excel application.
hwnd = FindWindow("xlMain", Application.Caption)
'Restore system menu to original state.
hMenu% = GetSystemMenu(hwnd, 1)
End Sub

Try some code like the following:


Private Const WS_MAXIMIZEBOX = &H10000
Private Const WS_MINIMIZEBOX = &H20000
Private Const GWL_STYLE = (-16)

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

Sub ShowMinMax(ShowMin As Boolean, ShowMax As Boolean)
Dim WinInfo As Long
WinInfo = GetWindowLong(Application.HWnd, GWL_STYLE)

If ShowMin = True Then
WinInfo = WinInfo Or WS_MAXIMIZEBOX
Else
WinInfo = WinInfo And (Not WinInfo)
End If
If ShowMax = True Then
WinInfo = WinInfo Or WS_MAXIMIZEBOX
Else
WinInfo = WinInfo And (Not WS_MAXIMIZEBOX)
End If
SetWindowLong Application.HWnd, GWL_STYLE, WinInfo
End Sub



Cal the ShowMinMax proc passing it values indicating whether to show
or hide the min and max buttons.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Fri, 11 Dec 2009 11:47:01 -0800, Steve
wrote:

I am trying to prevent a workbook I made from being minimized/maximized by
the user. I have command buttons to do that and I want to force them to use
the command buttons. The code below disables the min/max application buttons
in Excel 2003, but it only disables the close button in Excel 2007. It also
disables dragging to resize the application window in 2007, but that doesn't
help me. I need to disable the min/max button and it needs to work in Excel
2007. Any suggestions?

Thanks

Steve

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

Declare Function GetSystemMenu Lib "user32" (ByVal hwnd As Long, _
ByVal bRevert As Integer) As Integer

Declare Function DeleteMenu Lib "user32" (ByVal hMenu As Integer, _
ByVal nPosition As Integer, ByVal wFlags As Integer) As Integer

'The following procedure disables the Control menu.
Sub Disable_Control()
Dim X As Integer, hwnd As Long
hwnd = FindWindow("XLMain", Application.Caption)
For X = 1 To 9
'Delete the first menu command and loop until
'all commands are deleted
Call DeleteMenu(GetSystemMenu(hwnd, False), 0, 1024)
Next X
End Sub

'The following procedure restores the Control menu.
'Note that to run this procedure, the Declare statements above
'must be in the module.
Sub RestoreSystemMenu()
Dim hwnd As Long
'Get the window handle of the Excel application.
hwnd = FindWindow("xlMain", Application.Caption)
'Restore system menu to original state.
hMenu% = GetSystemMenu(hwnd, 1)
End Sub

There are a few typos in my previous reply. Use the following code
instead.

Sub ShowMinMax(ShowMin As Boolean, ShowMax As Boolean)
Dim WinInfo As Long
WinInfo = GetWindowLong(Application.HWnd, GWL_STYLE)

If ShowMin = True Then
WinInfo = WinInfo Or WS_MINIMIZEBOX
Else
WinInfo = WinInfo And (Not WS_MINIMIZEBOX)
End If
If ShowMax = True Then
WinInfo = WinInfo Or WS_MAXIMIZEBOX
Else
WinInfo = WinInfo And (Not WS_MAXIMIZEBOX)
End If
SetWindowLong Application.HWnd, GWL_STYLE, WinInfo
End Sub


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Fri, 11 Dec 2009 16:14:31 -0600, Chip Pearson
wrote:

Try some code like the following:


Private Const WS_MAXIMIZEBOX = &H10000
Private Const WS_MINIMIZEBOX = &H20000
Private Const GWL_STYLE = (-16)

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

Sub ShowMinMax(ShowMin As Boolean, ShowMax As Boolean)
Dim WinInfo As Long
WinInfo = GetWindowLong(Application.HWnd, GWL_STYLE)

If ShowMin = True Then
WinInfo = WinInfo Or WS_MAXIMIZEBOX
Else
WinInfo = WinInfo And (Not WinInfo)
End If
If ShowMax = True Then
WinInfo = WinInfo Or WS_MAXIMIZEBOX
Else
WinInfo = WinInfo And (Not WS_MAXIMIZEBOX)
End If
SetWindowLong Application.HWnd, GWL_STYLE, WinInfo
End Sub



Cal the ShowMinMax proc passing it values indicating whether to show
or hide the min and max buttons.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Fri, 11 Dec 2009 11:47:01 -0800, Steve
wrote:

I am trying to prevent a workbook I made from being minimized/maximized by
the user. I have command buttons to do that and I want to force them to use
the command buttons. The code below disables the min/max application buttons
in Excel 2003, but it only disables the close button in Excel 2007. It also
disables dragging to resize the application window in 2007, but that doesn't
help me. I need to disable the min/max button and it needs to work in Excel
2007. Any suggestions?

Thanks

Steve

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

Declare Function GetSystemMenu Lib "user32" (ByVal hwnd As Long, _
ByVal bRevert As Integer) As Integer

Declare Function DeleteMenu Lib "user32" (ByVal hMenu As Integer, _
ByVal nPosition As Integer, ByVal wFlags As Integer) As Integer

'The following procedure disables the Control menu.
Sub Disable_Control()
Dim X As Integer, hwnd As Long
hwnd = FindWindow("XLMain", Application.Caption)
For X = 1 To 9
'Delete the first menu command and loop until
'all commands are deleted
Call DeleteMenu(GetSystemMenu(hwnd, False), 0, 1024)
Next X
End Sub

'The following procedure restores the Control menu.
'Note that to run this procedure, the Declare statements above
'must be in the module.
Sub RestoreSystemMenu()
Dim hwnd As Long
'Get the window handle of the Excel application.
hwnd = FindWindow("xlMain", Application.Caption)
'Restore system menu to original state.
hMenu% = GetSystemMenu(hwnd, 1)
End Sub