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

[surkdidat]

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.

 

[Logit]

.
To minimize confusion, what are you defining as the "title bar" ?

 

[Rick Rothstein]

If you copy/paste the following code into the UserForm's code window, the user form will be displayed without a titlebar or surrounding borders (make sure you leave yourself a way to dismiss the UserForm while testing this)...

Private Declare Function FindWindow Lib "user32" _
                Alias "FindWindowA" _
               (ByVal lpClassName As String, _
                ByVal lpWindowName As String) As Long

Private Declare Function GetWindowLong Lib "user32" _
                Alias "GetWindowLongA" _
               (ByVal hWnd As Long, _
                ByVal nIndex As Long) 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 DrawMenuBar Lib "user32" _
               (ByVal hWnd As Long) As Long

Dim hWndForm As Long

Private Sub UserForm_Initialize()
   Dim Style As Long, Menu As Long
   hWndForm = FindWindow("ThunderDFrame", Me.Caption)
   Style = GetWindowLong(hWndForm, &HFFF0)
   Style = Style And Not &HC00000
   SetWindowLong hWndForm, &HFFF0, Style
   DrawMenuBar hWndForm
End Sub

 

[surkdidat]

Thank you for this. Apologies been offline for a week, will try later!

If you copy/paste the following code into the UserForm's code window, the user form will be displayed without a titlebar or surrounding borders (make sure you leave yourself a way to dismiss the UserForm while testing this)...

Private Declare Function FindWindow Lib "user32" _
                Alias "FindWindowA" _
               (ByVal lpClassName As String, _
                ByVal lpWindowName As String) As Long

Private Declare Function GetWindowLong Lib "user32" _
                Alias "GetWindowLongA" _
               (ByVal hWnd As Long, _
                ByVal nIndex As Long) 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 DrawMenuBar Lib "user32" _
               (ByVal hWnd As Long) As Long

Dim hWndForm As Long

Private Sub UserForm_Initialize()
   Dim Style As Long, Menu As Long
   hWndForm = FindWindow("ThunderDFrame", Me.Caption)
   Style = GetWindowLong(hWndForm, &HFFF0)
   Style = Style And Not &HC00000
   SetWindowLong hWndForm, &HFFF0, Style
   DrawMenuBar hWndForm
End Sub

 

[surkdidat]

Apologies - to clarify, do both bits of code go into UserForm? What does the "user32" relate to? Is this the worksheet name?
Have tried as worksheet name, but it does not remove title bar?
Wll "Alt+F11"still allow me back in to editor with this code please?

 

[surkdidat]

Right at the top, with the minimise/maximise, close buttons, and the spreadsheet tite

.
To minimize confusion, what are you defining as the "title bar" ?

 

[Rick Rothstein]

Apologies - to clarify, do both bits of code go into UserForm? What does the "user32" relate to? Is this the worksheet name?
Have tried as worksheet name, but it does not remove title bar?
Wll "Alt+F11"still allow me back in to editor with this code please?

Just copy/paste the code exactly as I wrote it... don't change any part of it. And all of the code goes in the UserForm's code window. The first four Declare statements create a link to some of the underlying Windows API (Application Programming Interface) functions which are needed to affect the display of Windows components.

 

[surkdidat]

I have added this code into UserForm. I have saved and closed, and reopened. Still bring up Spreadsheet Go into editor, and run code. Just brings up grey box?

Just copy/paste the code exactly as I wrote it... don't change any part of it. And all of the code goes in the UserForm's code window. The first four Declare statements create a link to some of the underlying Windows API (Application Programming Interface) functions which are needed to affect the display of Windows components.

 

[Jaafar Tribak]

I have added this code into UserForm. I have saved and closed, and reopened. Still bring up Spreadsheet Go into editor, and run code. Just brings up grey box?

Do you have a userform in your workbook Project ? If so, what's the name of the UserForm ?

From the description you have given us so far, I think, you are referring to the title bar of the excel application not to that of a UserForm in which case the code that Rick kindly suggested won't work .

 

[Logit]

.
Paste in a Routine Module :

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 :

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


Private Sub Workbook_Open()
    Call hide_menu
End Sub