Solved: Mouse Right Click, Insert Row Disabled

[Ciorbea]

Hello,
Can you tell me, please, how to disable "Insert Row" when right click with the mouse (not the Insert Row control from the "Worksheet Menu Bar")? And if this is done, how can it be restored to Enabled?

Thank you,
Ciorbea

[Charlize]

Place the next code in the code for your sheet. It will disable the right mouse click (will send an esc key after the right click)
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
MsgBox ("u can not use this")
SendKeys "{ESC}"
End Sub
Charlize

Another way to do the same :

Sub DisableShortcutMenu()
CommandBars("Cell").Enabled = False
End Sub

To activate the menu again :


Sub EnableShortcutMenu()
CommandBars("Cell").Enabled = True
End Sub


Charlize

[mbarron]

Original post change to assign Control numbers instead of named option. Controls(5) versus Controls("Insert")

The following toggles the Insert option (on or off) for the Cell and Row right click menu based on the Row's current status.


Option Explicit

Sub toggleInsertRow()

Dim insertState As Boolean

insertState = Not CommandBars("Cell").Controls(5).Enabled

CommandBars("Row").Controls(5).Enabled = insertState
CommandBars("Cell").Controls(5).Enabled = insertState

End Sub

[Ciorbea]

You are great!
Thank you!