Hello,
Under the right-click menu I added an item on a specific range in Excel. The location in Excel knows what to add into the menu (a for creating a heading or a new line item). When I right-click somewhere else the custom menu is removed. So far so good.
When a right-click follows a right-click multiple times the menu item is added again and again. The routine should check weather the items exists and if now, create.
This is what I made (with some help of 'Google')
Right-click code:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Not Application.Intersect(Target, Me.Range("A13:A31”)) Is Nothing Then
' MsgBox ("This is the right-click range in this sheet.")
If Target.Cells.Value = "" Then Exit Sub 'gives an error when the full row is selected instead of one cell
If Target.Cells.Value Like "?0" Then
' MsgBox "Heading"
Run "CustomContextMenuKopjeAdd"
Run "CustomContextMenuPostRemove" 'when a heading, remove menu item
Else
' MsgBox "Item"
Run "CustomContextMenuKopjeRemove" 'when a item, remove menu heading
Run "CustomContextMenuPostAdd"
End If
Else
' No cell of Target in in the range A14:A31. Get Out.
Run "CustomContextMenuKopjeRemove"
Run "CustomContextMenuPostRemove"
Exit Sub
End If
End Sub
Create and remove code:
Private Sub CustomContextMenuPostAdd()
Dim ctrl As CommandBarControl
Dim btn As CommandBarControl
Dim i As Integer
'MsgBox "Context menu: make item"
'add a 'popup' control to the cell commandbar (menu)
Set ctrl = Application.CommandBars("Cell").Controls.Add _
(Type:=msoControlPopup, Before:=1)
ctrl.Caption = "SSK p&ost..."
'first sub item
Set btn = ctrl.Controls.Add
btn.Caption = "&Add" 'give them a name
btn.OnAction = "RowNew" 'the routine called by the control
'second sub item
Set btn = ctrl.Controls.Add
btn.Caption = "&Remove" 'give them a name
btn.OnAction = "RowRemove" 'the routine called by the control
End Sub
Private Sub CustomContextMenuPostRemove()
Dim ctrl As CommandBarControl
'MsgBox "Context menu: remove item"
'go thru all the cell commandbar controls and delete our menu item
For Each ctrl In Application.CommandBars("Cell").Controls
If ctrl.Caption = "SSK p&ost..." Then ctrl.Delete
Next
End Sub
Does anybody know what I did wrong or how I can improve this?
Thank you in advance!
Erik
Bookmarks