Disable Title Bar Double Click

[delboy01]

Hi,

I have a database running in excel 2010. I am trying to prevent the user from double clicking on the title bar which makes the program window "restore down".

I have some code which has disabled the control buttons in the top right (min,max and close) which prevents the user from resizing the window, but I am unable to catch the double click event to prevent the Title Bar from resizing.

I have had a look around for some code using excel 2010 but to no avail.

Hope that someone might have an idea to steer me in the right direction.

Many thanks

Regards
delboy01

 

[rjplante]

Could you please post the code to disable the double click on the title bar. I am using Excel 2007 and I would like to achieve the same thing.

Thanks,

Robert

 

[delboy01]

Hi There,

I have not worked out the code for the double click issue yet.

I only have the code to prevent the user from clicking on the 3 buttons on the right of the title bar.

Regards

 

[Jaafar Tribak]

AFAIK, this can only be achieved via subclassing the excel application main window or hooking the mouse. However both thecniques are unstable if done in VBA and could potentially crash the application.

This seems like overkill but for the sake of intellectual curiosity, I would like to know if this limitation could be overcome by runnig the code from a small external VB6 dll which could be dynamically loaded into the excel process upon opening the workbook.

If time permits,I'll take a look at this and see if anything nice comes up.

 

[John_w]

Not all my own work, but here is code which disables the double-click on the window caption bar, preventing the window being maximised or restored to its default size. It uses the subclassing technique mentioned by Jaafar.

Put this code in a standard module:

Option Explicit

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 CallWindowProc Lib "user32" Alias "CallWindowProcA" _
    (ByVal lpPrevWndFunc As Long, ByVal hWnd As Long, ByVal msg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
 
Private Const GWL_WNDPROC = -4
Private Const WM_DESTROY = &H2
Private Const WM_NCLBUTTONDBLCLK = &HA3
Private Const HTCAPTION = 2

Public hDefWindowProc As Long


Public Sub Set_WindowProc(hWnd As Long)

    If hDefWindowProc = 0 Then
    
        'Activate our WindowProc
        
        hDefWindowProc = SetWindowLong(hWnd, GWL_WNDPROC, AddressOf WindowProc)
        
    End If
    
End Sub
 
Public Sub Clear_WindowProc(hWnd As Long)
    
    Dim hOldWindowProc As Long
    
    If hDefWindowProc <> 0 Then
    
        'Restore default WindowProc
        
        hOldWindowProc = SetWindowLong(hWnd, GWL_WNDPROC, hDefWindowProc)
        hDefWindowProc = 0
        
    End If

End Sub

Private Function WindowProc(ByVal hWnd As Long, ByVal uMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
    
    If uMsg = WM_NCLBUTTONDBLCLK And wParam = HTCAPTION Then
        
        'Window Message indicates a double-click on the caption bar, so return 0 to indicate that this message has been
        'processed, thus ignoring the message
        
        WindowProc = 0
    
    Else
    
        'Clear our WindowProc if the window is being destroyed
        
        If uMsg = WM_DESTROY Then
            Clear_WindowProc Application.hWnd
        End If
    
        'Call default window procedure
        
        WindowProc = CallWindowProc(hDefWindowProc, hWnd, uMsg, wParam, lParam)

    End If
    
End Function

To disable or enable the double-click on the caption bar, call Set_WindowProc or Clear_WindowProc respectively, like this:

Set_WindowProc Application.hWnd

Clear_WindowProc Application.hWnd

Although the code seems to work, as noted by Jaafar, Excel sometimes crashed or froze after calling Set_WindowProc (I tested it by calling from inside command button click events). Also, it sometimes crashes if the VB Editor window is open or has been opened in the Excel session. Closing Excel completely and restarting usually fixed the problem. I'm using Excel 2003 on Win XP.

I have some code which has disabled the control buttons in the top right (min,max and close) which prevents the user from resizing the window

How are you doing this? Post your code. The SetWindowLong function, as used in my code above, can also remove the minimise, maximise/restore and close buttons on a window.

 

[delboy01]

Thanks for posting your code john.

Unfortunatally it did not work when testing in excel 2010 on windows 7 and windows vista operating system.

It did not seem do have any affect at all, but it did freeze excel once and I had to close the program.

Below is the code I am using which disables the 3 title bar button, the buttons are still visible and change colour when you hover over them but are not active.
I had some code working which removed 2 of the 3 buttons but this resized the title bar so it was smaller and changed the whole window slightly left due to the resize, hence why I am using the code below.

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
 
 
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

Regards
Delboy