Right click menu with Conditional Formatting and Validation

User avatar
ErikJan
5StarLounger
Posts: 1185
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Right click menu with Conditional Formatting and Validation

Post by ErikJan »

I created a VBA sheet in the past that I could run once and from that moment forward the right-click menu in existing and new sheets would show two additional options: Conditional Formatting" and "Validation".

I always used that and find it extremely handy.

On one of my new systems I use (Win10/64; MS365/32 v2002) this seems to work when I run the special sheet but it doesn't 'stick'.

Anyone with a hint?

PS. Here's the code

Code: Select all

Sub EJ_Right_Click()
Application.CommandBars("Cell").Reset 'to reset any changes made
With Application.CommandBars("Cell").Controls.Add(msoControlButton, 3058, , 5, False)
    .Caption = "Conditional Formatting..."
    '.FaceId = 481
End With
'
With Application.CommandBars("Cell").Controls.Add(msoControlButton, 2034, , 5, False)
    .Caption = "Validation..."
End With
End Sub

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Right click menu with Conditional Formatting and Validation

Post by HansV »

Try the following (it will remove all customization from commandbars):
- Quit Excel.
- In File Explorer, navigate to %appdata%\Microsoft\Excel
- Rename the file Excel15.xlb to Excel15.old.
- Start Excel and run your macro.
- Check whether the change sticks now.
Best wishes,
Hans