a big problem after run macro in exel

A

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,429
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
hello
i have a big problem when i tested this code type of protection it hide command bar when i right click does not show but when i change to true it also doesn't change it supposes showing the menu when right click
this is the macro
VBA Code: 
Sub cc()
  Application.CommandBars("Worksheet Menu Bar").Enabled = false

End Sub
thanks

thanks
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
VBA Code: 
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

Here is a simpler version :

Code: 
Option Explicit
Sub shwMenu()
 Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"

End Sub
Sub hideMenu()
 Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
End Sub
 
Upvote 0
hi, logit thanks for your trying but it doesn't show command bar when i right click for any cell or row ol column nothing work
 
Upvote 0
That requires additional coding both in the ThisWorkbook module and in the Regular module.

In ThisWorkbook module :

VBA Code: 
Option Explicit

Private Sub Workbook_Deactivate()

    On Error Resume Next

            With Application

                .CommandBars("Cell").Controls("RunIt").Delete

            End With

    On Error GoTo 0

End Sub


'Add Excel Right Click Menu

'Excel VBA: Adding a Command Button to the Excel Right Click Pop-up Toolbar/Command Bar
'Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only $145.00. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

'Adding a Command Button to the Excel Right Click Pop-up Toolbar/Command Bar

'Note how the code first deletes any existing menu option called "My Macro". This stops the code adding an additional custom menu options and ensures only one custom menu option is available at any one time. The use of Temporary:=True ensures this menu option is deleted whenever Excel closes. Without it, it's possible to have you custom menu option showing in another Workbook. This is also why we make use of the Workbook_Deactivate Event. That is, if we activate another Workbook while this one is open, the custom menu option is deleted.

'The macro that is run whenever this custom menu option is clicked is called "My_Macro". You should change this, and the caption, to suit your needs.

'The code below should be placed in Private Module of the Workbook Object (ThisWorkbook). To get there quickly, right click on the Excel icon, top left next to File and choose View Code.




Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

Dim cBut As CommandBarButton



    On Error Resume Next

        With Application

            .CommandBars("Cell").Controls("RunIt").Delete

            Set cBut = .CommandBars("Cell").Controls.Add(Temporary:=True)

        End With

        

        With cBut

           .Caption = "RunIt"

           .Style = msoButtonCaption

           .OnAction = "RunIt"

        End With

    On Error GoTo 0

End Sub

The above will add another menu item to the Right Click menu ... at the bottom. This is an example ... you will need to change the name of the macro that
is run when the Right Click Menu Selection "RunIt" is clicked. The menu name RunIt can be change to anything so long as you correctly edit the macro. In this
case it would be HideMenu.
 
Upvote 0
sorry logit i no know if i don't know how works i run macro workbook deactivate but nothing happened could you guide how i can run
 
Upvote 0
In a regular module ( I made a small change to the code ) :

VBA Code: 
Option Explicit

Sub shwMenu()
 Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
 DeleteRightClickMenu
End Sub
Sub hideMenu()
 Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
 DeleteRightClickMenu
End Sub


Sub DeleteRightClickMenu()
On Error Resume Next
'Range right click
     CommandBars("cell").Reset
'Table right click
     Application.CommandBars("List Range Popup").Reset
End Sub

In ThisWorkBook module :

Code: 
Option Explicit

Private Sub Workbook_Deactivate()

    On Error Resume Next

            With Application

                .CommandBars("Cell").Controls("hideMenu").Delete
                .CommandBars("Cell").Controls("shwMenu").Delete

            End With

    On Error GoTo 0

End Sub


'Add Excel Right Click Menu

'Excel VBA: Adding a Command Button to the Excel Right Click Pop-up Toolbar/Command Bar
'Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only $145.00. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

'Adding a Command Button to the Excel Right Click Pop-up Toolbar/Command Bar

'Note how the code first deletes any existing menu option called "My Macro". This stops the code adding an additional custom menu options and ensures only one custom menu option is available at any one time. The use of Temporary:=True ensures this menu option is deleted whenever Excel closes. Without it, it's possible to have you custom menu option showing in another Workbook. This is also why we make use of the Workbook_Deactivate Event. That is, if we activate another Workbook while this one is open, the custom menu option is deleted.

'The macro that is run whenever this custom menu option is clicked is called "My_Macro". You should change this, and the caption, to suit your needs.

'The code below should be placed in Private Module of the Workbook Object (ThisWorkbook). To get there quickly, right click on the Excel icon, top left next to File and choose View Code.




Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

Dim cBut As CommandBarButton
Dim cBut2 As CommandBarButton



    On Error Resume Next

        With Application

            .CommandBars("Cell").Controls("hideMenu").Delete
            .CommandBars("Cell").Controls("shwMenu").Delete

            Set cBut = .CommandBars("Cell").Controls.Add(Temporary:=True)
            Set cBut2 = .CommandBars("Cell").Controls.Add(Temporary:=True)

        End With

        

        With cBut

           .Caption = "Hide Menu"

           .Style = msoButtonCaption

           .OnAction = "hideMenu"

        End With
        
        With cBut2

           .Caption = "Show Menu"

           .Style = msoButtonCaption

           .OnAction = "shwMenu"

        End With


    On Error GoTo 0

End Sub


Download example workbook : Hide Menu Right Click.xlsm
 
Upvote 0
about your file i can't download it open amazon website and about your codes i put as what you said as for macros theses shwMenu , hideMenu this works for tool bar not command bar overall i run every macro but nothing happened is there some steps have to do ?
 
Upvote 0
Are you saying you want to hide the entire RIGHT CLICK MENU ? So when a user tries to use the RIGHT CLICK ... nothing shows and nothing happens ?
 
Upvote 0
actually i hided as in post #1 and i try show right click menu again if your macros do that nothing shows when right click
 
Upvote 0
You must log in or register to reply here.

Similar threads

X
  • Question
Remove filename only on title bar & not also on taskbar item?
Replies
0
Views
147
xunil76
X
F
  • Question
Problem with "Worksheet_BeforeRightClick"
Replies
1
Views
134
Fibo
F
K
  • Question
VBA code asking for Macro
Replies
9
Views
572
KMH
K
T
  • Question
RUN TIME ERROR '-2147024809
Replies
2
Views
120
TBZ120
T
O
  • Question
Issue Hiding Sheets with Command button
Replies
7
Views
294
Fluff
Fluff

Forum statistics

Threads
1,215,338
Messages
6,124,358
Members
449,155
Latest member
ravioli44

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top