Custom Right Click--VBA?

[dgold82]

I have an interesting project here. I have disabled all the command bars and
toolbars with VBA code--which is great for my purposes--but I would like to
create a custom right click (not a toolbar which I know how to do) with
certain commands. Is that possible? I would like to put a couple things like
"clear contents" for a radio button and print sheet and "Home" which would
hyperlink my user back to a certain worksheet.

Can anyone help me with code to do this? Here is what I use now to disable
everything:

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.DisplayFormulaBar = false
ActiveWindow.DisplayHeadings = false
Application.DisplayStatusBar = false
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",false)"

'This will disable all Command bars
Dim Cbar As CommandBar
For Each Cbar In Application.CommandBars
Cbar.Enabled = false
Next

End Sub


Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.DisplayFormulaBar = True
ActiveWindow.DisplayHeadings = True
Application.DisplayStatusBar = True
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",true)"

'This will disable all Command bars
Dim Cbar As CommandBar
For Each Cbar In Application.CommandBars
Cbar.Enabled = True
Next
End Sub

 

[RB Smissaert]

This code will give you the idea:


Sub TakeOverCellRightClick()
CustomCellRightClickMenu True
End Sub

Sub AddCellRightClick()
CustomCellRightClickMenu False
End Sub

Sub CustomCellRightClickMenu(bClearDefault As Boolean)

Dim oCtrl As Object

GetFormColoursFromINI

With Application.CommandBars("Cell")

If bClearDefault Then
'Clear the existing menus
For Each oCtrl In .Controls
oCtrl.Delete
Next oCtrl
Else
.Reset
End If

'add all the new menus
'---------------------
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "Frequency list from selected range (Ctrl + Shift + F)"
.OnAction = "FrequencyList"
.FaceId = 2119
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "Print all documents in selected range"
.OnAction = "PrintFilesInSheetRange"
.FaceId = 4
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "Reset cell right-click"
.OnAction = "ResetCellRightClickMenu"
.FaceId = 1293
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Full reset cell right-click"
.OnAction = "FullResetCellRightClickMenu"
.FaceId = 37
End With
End With

End Sub

Sub ResetCellRightClickMenu()

With Application.CommandBars("Cell")
.Reset
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "Add to cell right-click"
.OnAction = "AddCellRightClick"
.FaceId = 1047
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Reset cell right-click"
.OnAction = "ResetCellRightClickMenu"
.FaceId = 1293
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Full reset cell right-click"
.OnAction = "FullResetCellRightClickMenu"
.FaceId = 37
End With
End With

End Sub

Sub FullResetCellRightClickMenu()
Application.CommandBars("Cell").Reset
End Sub


RBS

 

[Patrick Molloy]

excel 2003

create a popup menu then activate it with the sheet's right click event.

 

[dgold82]

Thanks. This is definitely in the right direction. Got a few errors though.
On first run I had to remove "GetFormColoursFromINI"--don't know what that
does.

Second, I clicked on what on the custom entries you made and they didn't
work. Said that they didn't point to a macro.

Third, in the code that I listed below I had disabled all command bars so
that my users using excel 2003 wouldn't see any toolbars. When that is set to
false it won't bring up your custom right click.

Thoughts?

Second

 

[RB Smissaert]

I posted actual code that I have myself and that is just as an example.
It won't work with you as it is as you haven't got the actions (procedure)
that I have. That is why I said: This will give you the idea.
You have to adapt it to your needs and you can't disable the Cell
right-click toolbar, which looks a bad idea in any case.

RBS