Hide all borderes, toolbar, ribbons etc

[Peltz]

Hello there, need some help

Two questions:

1: I've looked around and compiled different codes in order to remove all infomation from the workbook beside the "grids" when opening. I'used this code, and before (silly me) I started moving things around and probably deleting stuff, worked like a charm.

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








Private Sub Workbook_Open()


'Clear all bars ribbons etc.
Dim ws As Worksheet
On Error Resume Next
    With Application
TempWS = ActiveSheet.Name
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
ActiveWindow.DisplayHeadings = True
ActiveWindow.DisplayWorkbookTabs = True
Next
Worksheets(ws).Activate
        .DisplayHorizontalScrollBar = True
        .DisplayVerticalScrollBar = True
        .CommandBars("Worksheet Menu Bar").Enabled = True
        .DisplayWorkbookTabs = True
        .DisplayGridlines = True
        .DisplayHeadings = True
        .DisplayOutline = True
        .DisplayWorkbookTabs = True
        .DisplayTitleBar = True
     End With
        With Application


           .DisplayFullScreen = False


           .CommandBars("MyToolbar").Enabled = False


           .CommandBars("Worksheet Menu Bar").Enabled = True
End With










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

I get an error at this line:

TempWS = ActiveSheet.Name

... and cant really figure out why. I dont really know if the rest of the code workes as intended any more, but I'll leave that for now.

Second:
I use this to close excel:

Private Sub CommandButton4_Click()




Dim ws As Worksheet
On Error Resume Next
    With Application
TempWS = ActiveSheet.Name
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
ActiveWindow.DisplayHeadings = True
ActiveWindow.DisplayWorkbookTabs = True
Next
Worksheets(ws).Activate
        .DisplayHorizontalScrollBar = True
        .DisplayVerticalScrollBar = True
        .CommandBars("Worksheet Menu Bar").Enabled = True
        .DisplayWorkbookTabs = True
        .DisplayGridlines = True
        .DisplayHeadings = True
        .DisplayOutline = True
        .DisplayWorkbookTabs = True
        .DisplayTitleBar = True
     End With
        With Application


           .DisplayFullScreen = False


           .CommandBars("MyToolbar").Enabled = False


           .CommandBars("Worksheet Menu Bar").Enabled = True
End With




Application.DisplayFullScreen = True
Application.Quit
Application.DisplayAlerts = False
ActiveWorkbook.Close False






End Sub

I get the same error at this line:

TempWS = ActiveSheet.Name

In adition: When opening excel afterwards, the toolbar and various other things are closed. I just need to press esc and it turns up again (and is'nt a problem te next time I open excel), the problem is that I intend to send the file to coworkers that are less than savvy working in excel and will think they've deleted internet...

Thank you, any help would be appreciated

 

[Rorylfc]

I believe the way to fix this is to define TempWS as Worksheet.

Add

 DIM TempWS AS Worksheet

and it should not error

 

[Peltz]

Hello... thanks for you help. However my code got so messy and redundanty that I gave up. Stripped it down again. This is what i ended up with.

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


Sub CommandButton4_Click()
  ShowTitleBar1 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


Sub ShowTitleBar1(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
   


Application.DisplayFullScreen = True
Application.Quit
Application.DisplayAlerts = False
ActiveWorkbook.Close False


End Sub

I dont know why the code removes everything besides the cells. When trying the code in a empty worksheet, it opens with scrollers, headings and such). However, as the code does what its supposed to do in this regard (just leaving the cells) i'm fine with that. There are unfortunatly three issues:

1: The code toggles back and fourth, so every second time I open the document, it will ether show with titlebar (and workbar).

2: I get a error if i try to run the code in sheet_1 where the button is located. That is, I copy the above code into sheet-1. However leaving the code in the workbook module, I dont get any errors, and it quits excel. The point of the code was to swith the titlebar back on, leaving excel at default.

3: The state of excel when quiting (wheter titlebar and toolbar shows), affects excel when opening a new workbook. So I sometimes end up with a workbook without titlebar and toolbar. This is fine by me as I just press esc, bu as I mentioned above, but as I mentioned before, I dont think my colleagues woun't be too happy.




Any help would be much appreciated, thanks

 

[Peltz]

By the way. Is there a way to trigger this code from a commandbutton or similar from Sheet1 when the code is placed in the "workbook" module?

 

[Jimbola]

Why not just switch to Fullscreen mode using

Application.DisplayFullScreen = True

and

Application.DisplayFullScreen = False

 

[Peltz]

Thanks for your reply. The reason is that "my" code removes the windows titlebar.

 

[Peltz]

BUT, that gave me an idea.
When running Application.DisplayFullScreen = False (and afterwards quitting excel without saving), It works perfectly. The workbook opens as it shoud, as well as new workbooks!! THANK A BUNCH!