Excel Title Bar

markbarr

Board Regular
Joined
Aug 18, 2013
Messages
88
Hi Guys

Does anyone have any examples or a tutorial showing me how to remove the excel title bar?

I mean the one with Microsoft Excel and the close buttons on

I'm told its possible and I have some code but don't know what part goes where

Any help appreciated

Mark
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,324
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Cross-posted here Excel Title Bar

Please take the time to have a read of the Forum Rules in particular #24 (there is a link there explaining why).
 

markbarr

Board Regular
Joined
Aug 18, 2013
Messages
88
sorry for cross posting

VOG
thats the code I have but i dont know what part goes where I am a newbie

does it all go in a module how do you call it etc..

thanks

mark
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,324
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Put the whole code in a regular module (Alt-F11, Insert, module, paste the code in the white space).

If there any red lines showing in the code post back

To run Alt-F8 click Title_Hide and click run
 

markbarr

Board Regular
Joined
Aug 18, 2013
Messages
88
Mark
Loads of red lines usually around where the line is split.

I changed them and it works in a blank workbook but not when i call it from workbook activate.

the code I am using is as follows

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Worksheets("Sheet1").Activate
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Enabled = True
Application.DisplayFullScreen = True
Application.Caption = "- Sodexo"
ActiveWindow.Caption = " "
Application.DisplayStatusBar = False
Application.DisplayFormulaBar = False
ActiveWindow.DisplayHeadings = False
ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.DisplayFormulas = False
Worksheets("Sheet1").Protect UserInterfaceOnly:=True
Worksheets("Sheet1").EnableSelection = xlNone
Call Title_Hide
Application.DisplayAlerts = True
Application.ScreenUpdating = True

do i need to change something?

Thanks for the advice
Mark
 

markbarr

Board Regular
Joined
Aug 18, 2013
Messages
88

ADVERTISEMENT

Mark

I have tried deleting all the code and just using the call but that still doesnt work

Mark
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,324
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Remove all the code (including the and replace it with the code below

Code:
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 Title_Show()

ShowTitleBar True

End Sub



Sub Title_Hide()

ShowTitleBar False

End Sub



Sub ShowTitleBar(bShow As Boolean)

Dim lStyle As Long

Dim tRect As RECT

Dim sWndTitle As String

Dim xlhnd



'// Untested should perhaps look for the class ?!

sWndTitle = "Microsoft Excel - " & ActiveWindow.Caption

xlhnd = FindWindow(vbNullString, sWndTitle)



'// Get the window's position:

GetWindowRect xlhnd, tRect



'// Show the Title bar ?

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

    lStyle = lStyle And Not WS_CAPTION

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



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

then in your workbook module put
Code:
Private Sub Workbook_Open()
Title_Hide
End Sub
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,324
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
jcargile made an adaptation to Ivan F Moala's code which hides everything but at present I haven't been through it to see if it can be amended to only hide the title bar (and being honest I don't feel like playing with it today to see if it is possible).
The code is below if you (or anyone else) wants to have a go playing with it.
Run Title_Toggle sub to hide/unhide.

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 Title_Toggle()
    ShowTitleBar Application.DisplayFullScreen
End Sub

Sub ShowTitleBar(bShow As Boolean)
Dim lStyle As Long
Dim tRect As RECT
Dim sWndTitle As String
Dim xlHnd

'// Find window handle
sWndTitle = Application.Caption '// Old code used "Microsoft Excel - " & ActiveWindow.Caption
xlHnd = FindWindow("XLMAIN", sWndTitle) '// Old code did not have class name

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

'// Show the Title bar ?
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
    lStyle = lStyle And Not WS_CAPTION
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
Application.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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,948
Messages
5,525,803
Members
409,663
Latest member
littleriver

This Week's Hot Topics

Top