Disable Title Bar Double Click

delboy01

New Member
Joined
Apr 2, 2011
Messages
5
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

rjplante

Active Member
Joined
Oct 31, 2008
Messages
477
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

New Member
Joined
Apr 2, 2011
Messages
5
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
:cool:
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,939
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,431
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:
Code:
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:
Code:
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

New Member
Joined
Apr 2, 2011
Messages
5
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.


Code:
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
:cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,109,359
Messages
5,528,228
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top