Disable all controls and menus except the "close", "minimize" and "maximize"

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,810
Office Version
  1. 2016
Platform
  1. Windows
So I have been digging the web for a while now and I can't find the best way to handle the above challenge yet.

Can it be possible to disable all the menus except the control box items as indicated in the header of this thread?

Regards
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,697
.
Paste these macros in the THISWORKBOOK module :

Code:
Option Explicit


Private Sub Workbook_Open()
    'These commands disable the menu bar.
   ' Application.DisplayFormulaBar = False
   ' Application.DisplayStatusBar = False
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'These commands re-enable the menu bar so other workbooks
    'won't be affected.
    'Application.DisplayFormulaBar = True
    'Application.DisplayStatusBar = True
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
End Sub
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,810
Office Version
  1. 2016
Platform
  1. Windows
.
Paste these macros in the THISWORKBOOK module :

Code:
Option Explicit


Private Sub Workbook_Open()
    'These commands disable the menu bar.
   ' Application.DisplayFormulaBar = False
   ' Application.DisplayStatusBar = False
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'These commands re-enable the menu bar so other workbooks
    'won't be affected.
    'Application.DisplayFormulaBar = True
    'Application.DisplayStatusBar = True
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
End Sub


Okay thanks.

I have these codes already.

They do hide those bars and menus.

I am hoping to have the ribbon shown but make all the menus inactive.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,697
Perhaps this ?

Code:
Option Explicit


Private Sub Workbook_Activate()
    If Application.CommandBars("Ribbon").Height > 100 Then
        SendKeys "^{F1}"
        DoEvents
    End If
End Sub


Private Sub Workbook_Deactivate()
    If Application.CommandBars("Ribbon").Height < 100 Then
        SendKeys "^{F1}"
        DoEvents
    End If
End Sub
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,810
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

It did nothing.
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,810
Office Version
  1. 2016
Platform
  1. Windows
Sure I did.

And also with the workbook you posted. It only minimizes the ribbon to leave only the menus.

Meanwhile,
I would wish to have the menus deactivated.

I shouldn't be able to interact with them with the mouse.

I should only be able to access them back with a reverse code.

Kind of scary I think.:)
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,697
.
The code in Post #2 will do that. It might not look like you were expecting, but it is the answer.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,816
Messages
5,542,656
Members
410,566
Latest member
Jonniehoffman
Top