Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Excel Title Bar

This is a discussion on Excel Title Bar within the Excel Questions forums, part of the Question Forums category; Hi Guys Does anyone have any examples or a tutorial showing me how to remove the excel title bar? I ...

  1. #1
    Board Regular
    Join Date
    Aug 2013
    Posts
    88

    Default Excel Title Bar

    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

  2. #2
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,331

    Default Re: Excel Title Bar

    See the code from Ivan F Moala here Is it possible to hide the Excel Title Bar?

    This goes in a regular module.
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    2,727

    Default Re: Excel Title Bar

    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).
    Using Excel 2010 32 bit on Windows 64 bit

    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please try & follow the posting Rules and Guidelines & please use CODE tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links:

    Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste

  4. #4
    Board Regular
    Join Date
    Aug 2013
    Posts
    88

    Default Re: Excel Title Bar

    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

  5. #5
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    2,727

    Default Re: Excel Title Bar

    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
    Using Excel 2010 32 bit on Windows 64 bit

    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please try & follow the posting Rules and Guidelines & please use CODE tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links:

    Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste

  6. #6
    Board Regular
    Join Date
    Aug 2013
    Posts
    88

    Default Re: Excel Title Bar

    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

  7. #7
    Board Regular
    Join Date
    Aug 2013
    Posts
    88

    Default Re: Excel Title Bar

    Mark

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

    Mark

  8. #8
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    2,727

    Default Re: Excel Title Bar

    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 by MARK858; Sep 8th, 2013 at 06:22 AM.
    Using Excel 2010 32 bit on Windows 64 bit

    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please try & follow the posting Rules and Guidelines & please use CODE tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links:

    Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste

  9. #9
    Board Regular
    Join Date
    Aug 2013
    Posts
    88

    Default Re: Excel Title Bar

    hi mark

    sorry doesn't work just gets rid of everything EXCEPT the title bar

    Link to my skydrive with example file

    https://skydrive.live.com/redir?resi...HuELaHs9PxD1sM

    Mark

  10. #10
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    2,727

    Default Re: Excel Title Bar

    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
    Using Excel 2010 32 bit on Windows 64 bit

    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please try & follow the posting Rules and Guidelines & please use CODE tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links:

    Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com