VBA to remove "Title Bar" (Minimise/Maximise/Spreadsheet Name/Close

S

surkdidat

Well-known Member
Joined
Oct 1, 2011
Messages
597
Office Version
  1. 365
Can someone help me with this please?!

I need code in my spreadsheet to stop users having access to the "title bar".

I have VBA written so there is a "close button" on my spreadsheet so the user closes the sheet (and saves automatically)

EDIT: I do not need the code to do anything else, as the window is set for a specific size, and resizes dependant on what the user needs to input. The scrollbar, tabs, row and column heading, formula bar are already hidden.
 
Last edited:
Sort by date Sort by votes
Hi

Unfortunately, this is not doing what I need. As mentioned above, I need the window to stay a specific size, (as it resizes as req.) Also, my code already hides the menu bar, vertical/horizontal scroll bar, tabs etc.

It is purely the "title bar" when in sheet mode (one that has the spreadsheet name, minimise/maximise and the close 'X' on it that I need to have hidden

Logit said:
.
Paste in a Routine Module :

Code: 
Option Explicit


Sub hide_menu()


With Worksheets("Sheet1")


    With ActiveWindow
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = False
    End With
    With Application
        .DisplayFullScreen = True
        .DisplayFormulaBar = False
        .DisplayStatusBar = False
    End With
    With Application
    
        .CommandBars("Full Screen").Visible = False
        .CommandBars("Worksheet Menu Bar").Enabled = False
        .CommandBars("Standard").Visible = False
        .CommandBars("Formatting").Visible = False
    End With
End With
End Sub


Sub unhide_menu()


With Worksheets("Sheet1")


    With ActiveWindow
        .DisplayHorizontalScrollBar = True
        .DisplayVerticalScrollBar = True
    End With
    With Application
        .DisplayFullScreen = False
        .DisplayFormulaBar = True
        .DisplayStatusBar = True
    End With
    With Application
        .CommandBars("Full Screen").Visible = True
        .CommandBars("Worksheet Menu Bar").Enabled = True
        .CommandBars("Standard").Visible = True
        .CommandBars("Formatting").Visible = True
    End With
End With
End Sub



Paste in ThisWorkbook module :

Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call unhide_menu
End Sub


Private Sub Workbook_Open()
    Call hide_menu
End Sub
Click to expand...
 
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
.
The following, using API calls, will accomplish the goal. It does not include saving / naming the file or removing the file name from the top of the window.
To go full screen, right click the sheet and click the bottom menu selection or double-click the white color bar at top.

Code: 
Option Explicit


Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) 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 GetWindowLong Lib "user32" Alias "GetWindowLongA" _
    (ByVal hwnd As Long, ByVal nIndex As Long) As Long


Private Const GWL_STYLE = (-16)
Private Const WS_CAPTION = &HC00000
Private Const WS_MAXIMIZEBOX = &H10000
Private Const WS_MINIMIZEBOX = &H20000
Private Const WS_SYSMENU = &H80000


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 Enum ESetWindowPosStyles
    SWP_SHOWWINDOW = &H40
    SWP_HIDEWINDOW = &H80
    SWP_FRAMECHANGED = &H20
    SWP_NOACTIVATE = &H10
    SWP_NOCOPYBITS = &H100
    SWP_NOMOVE = &H2
    SWP_NOOWNERZORDER = &H200
    SWP_NOREDRAW = &H8
    SWP_NOREPOSITION = SWP_NOOWNERZORDER
    SWP_NOSIZE = &H1
    SWP_NOZORDER = &H4
    SWP_DRAWFRAME = SWP_FRAMECHANGED
    HWND_NOTOPMOST = -2
End Enum


Private Declare Function GetWindowRect Lib "user32" _
    (ByVal hwnd As Long, lpRect As RECT) As Long


Private Type RECT
    Left As Long
    Top As Long
    Right As Long
    Bottom As Long
End Type


Sub ShowTitleBar(xlApp As Excel.Application, bShow As Boolean, Optional bCaptionOverride As Boolean = True)
Dim lStyle As Long
Dim tRect As RECT
'Dim sWndTitle As String
Dim xlhnd


'## I modified this function to receive an Application instance and
'## to use it's .Hwnd property rather than the FindWindow API call
xlhnd = xlApp.hwnd


'// Get the window's position:
GetWindowRect xlhnd, tRect


If Not bShow Then
    lStyle = GetWindowLong(xlhnd, GWL_STYLE)
    lStyle = lStyle And Not WS_SYSMENU
    lStyle = lStyle And Not WS_MAXIMIZEBOX
    lStyle = lStyle And Not WS_MINIMIZEBOX
    '## I added this logic to ensure the CAPTION may always displayed if bCaptionOverride
    If Not bCaptionOverride Then
        lStyle = lStyle And Not WS_CAPTION
    Else
        lStyle = lStyle Or WS_CAPTION
    End If
Else
    lStyle = GetWindowLong(xlhnd, GWL_STYLE)
    lStyle = lStyle Or WS_SYSMENU
    lStyle = lStyle Or WS_MAXIMIZEBOX
    lStyle = lStyle Or WS_MINIMIZEBOX
    lStyle = lStyle Or WS_CAPTION
End If


SetWindowLong xlhnd, GWL_STYLE, lStyle


xlApp.DisplayFullScreen = Not bShow


'// Ensure the style is set and makes the xlwindow the
'// same size, regardless of the title bar.
SetWindowPos xlhnd, 0, tRect.Left, tRect.Top, tRect.Right - tRect.Left, _
    tRect.Bottom - tRect.Top, SWP_NOREPOSITION Or SWP_NOZORDER Or SWP_FRAMECHANGED


End Sub


Sub Test1()
Dim xl As New Excel.Application


xl.Workbooks.Add
Application.Caption = " "
ShowTitleBar xl, False


' configure the application


xl.Visible = True


' Re-enable the ribbon, or the user can double-click the title bar or Restore menu.
ShowTitleBar xl, False


End Sub
 
Upvote 0
Logit said:
.
The following, using API calls, will accomplish the goal. It does not include saving / naming the file or removing the file name from the top of the window.
To go full screen, right click the sheet and click the bottom menu selection or double-click the white color bar at top.

Code: 
Option Explicit


Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) 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 GetWindowLong Lib "user32" Alias "GetWindowLongA" _
    (ByVal hwnd As Long, ByVal nIndex As Long) As Long


Private Const GWL_STYLE = (-16)
Private Const WS_CAPTION = &HC00000
Private Const WS_MAXIMIZEBOX = &H10000
Private Const WS_MINIMIZEBOX = &H20000
Private Const WS_SYSMENU = &H80000


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 Enum ESetWindowPosStyles
    SWP_SHOWWINDOW = &H40
    SWP_HIDEWINDOW = &H80
    SWP_FRAMECHANGED = &H20
    SWP_NOACTIVATE = &H10
    SWP_NOCOPYBITS = &H100
    SWP_NOMOVE = &H2
    SWP_NOOWNERZORDER = &H200
    SWP_NOREDRAW = &H8
    SWP_NOREPOSITION = SWP_NOOWNERZORDER
    SWP_NOSIZE = &H1
    SWP_NOZORDER = &H4
    SWP_DRAWFRAME = SWP_FRAMECHANGED
    HWND_NOTOPMOST = -2
End Enum


Private Declare Function GetWindowRect Lib "user32" _
    (ByVal hwnd As Long, lpRect As RECT) As Long


Private Type RECT
    Left As Long
    Top As Long
    Right As Long
    Bottom As Long
End Type


Sub ShowTitleBar(xlApp As Excel.Application, bShow As Boolean, Optional bCaptionOverride As Boolean = True)
Dim lStyle As Long
Dim tRect As RECT
'Dim sWndTitle As String
Dim xlhnd


'## I modified this function to receive an Application instance and
'## to use it's .Hwnd property rather than the FindWindow API call
xlhnd = xlApp.hwnd


'// Get the window's position:
GetWindowRect xlhnd, tRect


If Not bShow Then
    lStyle = GetWindowLong(xlhnd, GWL_STYLE)
    lStyle = lStyle And Not WS_SYSMENU
    lStyle = lStyle And Not WS_MAXIMIZEBOX
    lStyle = lStyle And Not WS_MINIMIZEBOX
    '## I added this logic to ensure the CAPTION may always displayed if bCaptionOverride
    If Not bCaptionOverride Then
        lStyle = lStyle And Not WS_CAPTION
    Else
        lStyle = lStyle Or WS_CAPTION
    End If
Else
    lStyle = GetWindowLong(xlhnd, GWL_STYLE)
    lStyle = lStyle Or WS_SYSMENU
    lStyle = lStyle Or WS_MAXIMIZEBOX
    lStyle = lStyle Or WS_MINIMIZEBOX
    lStyle = lStyle Or WS_CAPTION
End If


SetWindowLong xlhnd, GWL_STYLE, lStyle


xlApp.DisplayFullScreen = Not bShow


'// Ensure the style is set and makes the xlwindow the
'// same size, regardless of the title bar.
SetWindowPos xlhnd, 0, tRect.Left, tRect.Top, tRect.Right - tRect.Left, _
    tRect.Bottom - tRect.Top, SWP_NOREPOSITION Or SWP_NOZORDER Or SWP_FRAMECHANGED


End Sub


Sub Test1()
Dim xl As New Excel.Application


xl.Workbooks.Add
Application.Caption = " "
ShowTitleBar xl, False


' configure the application


xl.Visible = True


' Re-enable the ribbon, or the user can double-click the title bar or Restore menu.
ShowTitleBar xl, False


End Sub
Click to expand...

Hi Logit,

I think the code you posted doesn't prevent resizing the excel window when double-clicking the titlebar and will not prevent closing excel via Alt+F4.

The closing part issue is easy to fix by just adding Application.OnKey "%{F4}", "" ... but preventing the resizing of excel when double-clicking the title bar is trickier to achieve short of subclassing excel.
 
Upvote 0
@Logit

I have just tested your code after editing it to work with excel 64 bit and it works well for completely removing the titlebar when setting the bCaptionOverride argument to False hence removing the WS_CAPTION style and finally using the SWP_FRAMECHANGED flag in the SetWindowPos API .

Nice solution Logit. Thank you.
 
Last edited:
Upvote 0
.
Thanks for the edits.

I've tried to remove the workbook name from the upper left corner but haven't been able to.

Any ideas ? Or is that permanent ?
 
Upvote 0
Logit said:
.
Thanks for the edits.

I've tried to remove the workbook name from the upper left corner but haven't been able to.

Any ideas ? Or is that permanent ?
Click to expand...

I'll take a look later and post back.
 
Upvote 0
Hi

This code is doing half of what I want - thanks! It is removing the menu bar (with code amendment as per below) - but it does not seem compatible with my existing code. And I need this to do this upon opening with the below. (I also have code in worksheets which deals with the resizing of the window dependent on use input, but cannot see that should impact having the code to remove the menu bar on opening

Code: 
rivate Sub Workbook_Open()






MsgBox "Version 4.01" & vbNewLine & "" & vbNewLine & "Please note xxxxxxxx"


Application.EnableEvents = True
Application.DisplayFormulaBar = False
Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"",False)"
    Application.DisplayStatusBar = False
    ActiveWindow.DisplayWorkbookTabs = False
    ActiveWindow.DisplayHeadings = False
ActiveSheet.EnableCalculation = True
    Range("C5,C6,C8,C9,C10,C13:C20,C22").Select
    
    Selection.ClearContents
    
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
  ActiveWorkbook.Protect Structure:=True, Windows:=False
   Range("C18:c20").Select
    ActiveCell.FormulaR1C1 = "Enter xxxx"
    ActiveSheet.EnableCalculation = True
        ActiveWindow.Zoom = 80
    iDesiredWidth = 620
    iDesiredHeight = 362
        
              ActiveWindow.Zoom = 80
                  With Application
        .WindowState = xlMaximized
        iMaxWidth = Application.Width
        iMaxHeight = Application.Height


        ' Adjust for starting point
        iMaxWidth = iMaxWidth - iStartX
        iMaxHeight = iMaxHeight - iStartY
        If iDesiredWidth > iMaxWidth Then
            iDesiredWidth = iMaxWidth
        End If
        If iDesiredHeight > iMaxHeight Then
            iDesiredHeight = iMaxHeight
        End If


        .WindowState = xlNormal
      
        .Width = iDesiredWidth
        .Height = iDesiredHeight
    End With
End Sub





Logit said:
.
The following, using API calls, will accomplish the goal. It does not include saving / naming the file or removing the file name from the top of the window.
To go full screen, right click the sheet and click the bottom menu selection or double-click the white color bar at top.

Code: 
Option Explicit


Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) 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 GetWindowLong Lib "user32" Alias "GetWindowLongA" _
    (ByVal hwnd As Long, ByVal nIndex As Long) As Long


Private Const GWL_STYLE = (-16)
Private Const WS_CAPTION = &HC00000
Private Const WS_MAXIMIZEBOX = &H10000
Private Const WS_MINIMIZEBOX = &H20000
Private Const WS_SYSMENU = &H80000


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 Enum ESetWindowPosStyles
    SWP_SHOWWINDOW = &H40
    SWP_HIDEWINDOW = &H80
    SWP_FRAMECHANGED = &H20
    SWP_NOACTIVATE = &H10
    SWP_NOCOPYBITS = &H100
    SWP_NOMOVE = &H2
    SWP_NOOWNERZORDER = &H200
    SWP_NOREDRAW = &H8
    SWP_NOREPOSITION = SWP_NOOWNERZORDER
    SWP_NOSIZE = &H1
    SWP_NOZORDER = &H4
    SWP_DRAWFRAME = SWP_FRAMECHANGED
    HWND_NOTOPMOST = -2
End Enum


Private Declare Function GetWindowRect Lib "user32" _
    (ByVal hwnd As Long, lpRect As RECT) As Long


Private Type RECT
    Left As Long
    Top As Long
    Right As Long
    Bottom As Long
End Type


Sub ShowTitleBar(xlApp As Excel.Application, bShow As Boolean, Optional bCaptionOverride As Boolean = True)
Dim lStyle As Long
Dim tRect As RECT
'Dim sWndTitle As String
Dim xlhnd


'## I modified this function to receive an Application instance and
'## to use it's .Hwnd property rather than the FindWindow API call
xlhnd = xlApp.hwnd


'// Get the window's position:
GetWindowRect xlhnd, tRect


If Not bShow Then
    lStyle = GetWindowLong(xlhnd, GWL_STYLE)
    lStyle = lStyle And Not WS_SYSMENU
    lStyle = lStyle And Not WS_MAXIMIZEBOX
    lStyle = lStyle And Not WS_MINIMIZEBOX
    '## I added this logic to ensure the CAPTION may always displayed if bCaptionOverride
    If Not bCaptionOverride Then
        lStyle = lStyle And Not WS_CAPTION
    Else
        lStyle = lStyle Or WS_CAPTION
    End If
Else
    lStyle = GetWindowLong(xlhnd, GWL_STYLE)
    lStyle = lStyle Or WS_SYSMENU
    lStyle = lStyle Or WS_MAXIMIZEBOX
    lStyle = lStyle Or WS_MINIMIZEBOX
    lStyle = lStyle Or WS_CAPTION
End If


SetWindowLong xlhnd, GWL_STYLE, lStyle


xlApp.DisplayFullScreen = Not bShow


'// Ensure the style is set and makes the xlwindow the
'// same size, regardless of the title bar.
SetWindowPos xlhnd, 0, tRect.Left, tRect.Top, tRect.Right - tRect.Left, _
    tRect.Bottom - tRect.Top, SWP_NOREPOSITION Or SWP_NOZORDER Or SWP_FRAMECHANGED


End Sub


Sub Test1()
Dim xl As New Excel.Application


xl.Workbooks.Add
Application.Caption = " "
ShowTitleBar xl, False


' configure the application


xl.Visible = True


' Re-enable the ribbon, or the user can double-click the title bar or Restore menu.
ShowTitleBar xl, False


End Sub
Click to expand...
 
Upvote 0
Thanks a lot for this piece of code to remove the title bar from my excel LeaderBoard sheet. I have been looking for this for ages. I have always been able to disable all Command bars and the Menu bar but was always annoyed that the Form title was always there at the top showing the Excel title. I am still using Excel 2003 and your code worked perfectly. Once again, thanks a lot, really appreciated.

Quinny Lawrence
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Hide userform title bar, and locking the userform into a relative position on the application.
2
Replies
16
Views
724
Jaafar Tribak
Jaafar Tribak
P
  • Solved
Disable 'X' on Worksheet
Replies
4
Views
179
Pacman52
P
D
  • Question Question
Modify a worksheet without registering it as a change
Replies
1
Views
128
Kaper
Kaper
A
  • Question Question
Open, Copy data and Close a workbook based on the names of the tabs in my active workbook
Replies
2
Views
137
Aurore87
A
I
  • Question Question
Show the "Edit Name" dialog from VBA for a specific Name
Replies
1
Views
883
Jaafar Tribak
Jaafar Tribak

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top