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

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,796
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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,602
.
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,796
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,602
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,796
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

It did nothing.
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,796
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,602
.
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,109,455
Messages
5,528,879
Members
409,844
Latest member
ardask
Top