Custom Right Click--VBA?

Discussion:

(too old to reply)

dgold82

2009-06-03 15:54:05 UTC

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

2009-06-03 17:41:59 UTC

Permalink

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

Post by 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
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

dgold82

2009-06-04 03:36:01 UTC

Permalink

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

Post by RB Smissaert
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

RB Smissaert

2009-06-04 12:41:17 UTC

Permalink

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

Post by 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

Post by 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
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

dgold82

2009-06-04 03:36:01 UTC

Permalink

Patrick Molloy

2009-06-03 17:47:32 UTC

Permalink

excel 2003

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

Patrick Molloy

2009-06-03 17:47:32 UTC

Permalink

excel 2003

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