on condition rightclick

[lior03]

hello
i want to make excel activate a right click shortcut only if the active cell contains a formula. i tried this:
Dim NewItem As CommandBarControl
If ActiveCell.HasFormula = True Then
Set NewItem = CommandBars("Cell").Controls.add
With NewItem
.Caption = "turn formula red"
.OnAction = "marco"
.BeginGroup = True
.FaceId = 353
End With
End If

in all other cases - an empty cell or cell without a formula i do not want this shortcut to appear.
thanks

[Bob Phillips]

hello
i want to make excel activate a right click shortcut only if the active cell contains a formula. i tried this:
Dim NewItem As CommandBarControl
If ActiveCell.HasFormula = True Then
Set NewItem = CommandBars("Cell").Controls.add
With NewItem
.Caption = "turn formula red"
.OnAction = "marco"
.BeginGroup = True
.FaceId = 353
End With
End If
in all other cases - an empty cell or cell without a formula i do not want this shortcut to appear.
thanks

You will need to trap the SelectionChange event and make the menu item visible or not depending on the cell contents, or create it as above, and delet it if not a formula.

[lior03]

dear xld
i manage to get what i wanted on a worksheet level.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
CommandBars("Cell").Controls("turn formula red").Delete
Dim NewItem As CommandBarControl
If IsEmpty(ActiveCell) Then Exit Sub
If ActiveCell.HasFormula = True Then
Set NewItem = CommandBars("Cell").Controls.Add
With NewItem
.Caption = "turn formula red"
.OnAction = "marco"
.BeginGroup = True
.FaceId = 353
End With
End If
End Sub
how can i make it work on a workbook level ?
thanks

[Bob Phillips]

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
CommandBars("Cell").Controls("turn formula red").Delete
On Error GoTo 0

Dim NewItem As CommandBarControl
If IsEmpty(ActiveCell) Then Exit Sub
If Target.HasFormula = True Then
Set NewItem = CommandBars("Cell").Controls.Add
With NewItem
.Caption = "turn formula red"
.OnAction = "marco"
.BeginGroup = True
.FaceId = 353
End With
End If
End Sub

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

Personally Moshe, I would set it up in the Workbook open, and just make non-visible/visible, but this should work okay.

[lior03]

dearest xld
i copy the code . why do i get a msgbox with error 91?

thanks

[Bob Phillips]

Because you coded it incorrectly



Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
Application.CommandBars("Cell").Controls("turn formula red").Delete
On Error GoTo 0

Dim NewItem As CommandBarControl
If IsEmpty(ActiveCell) Then Exit Sub
If Target.HasFormula = True Then
Set NewItem = Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, temporary:=True)
With NewItem
.Caption = "turn formula red"
.OnAction = "marco"
.BeginGroup = True
.FaceId = 353
End With
End If
End Sub

[lior03]

hello
letws complicate.
i am trying to add two on condition shortcuts.one as before will if a cell contain formula paint red all formula cells in currentregion.
the second will if a cell has comment autosize it.
my problem- if a cell has both a formula and a comment only one shortcut appear.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
Application.StatusBar = Sh.name & ":" & Target.Address
On Error Resume Next
Application.CommandBars("Cell").Controls("turn formula red").Delete
Application.CommandBars("Cell").Controls("resize comment").Delete
On Error GoTo 0
Dim NewItem As CommandBarControl
If IsEmpty(ActiveCell) Then Exit Sub
If Target.HasFormula = True Then
Set NewItem = Application.CommandBars("Cell").Controls.add(Type:=msoControlButton, temporary:=True)
With NewItem
.Caption = "turn formula red"
.OnAction = "marco"
.BeginGroup = True
.FaceId = 353
End With
End If
On Error Resume Next
Dim C As Comment
Set C = ActiveCell.Comment
If Not C Is Nothing Then
Set NewItem = CommandBars("Cell").Controls.add
NewItem.Caption = "resize comment"
NewItem.OnAction = "autofitcomments"
NewItem.FaceId = 687
NewItem.BeginGroup = True
End If
End Sub


thanks

[Bob Phillips]

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)

Application.StatusBar = Sh.Name & ":" & Target.Address
On Error Resume Next
Application.CommandBars("Cell").Controls("turn formula red").Delete
Application.CommandBars("Cell").Controls("resize comment").Delete
On Error GoTo 0
Dim NewItem As CommandBarControl
If IsEmpty(ActiveCell) Then Exit Sub
If Target.HasFormula = True Then
Set NewItem = Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, temporary:=True)
With NewItem
.Caption = "turn formula red"
.OnAction = "marco"
.BeginGroup = True
.Style = msoButtonIconAndCaption
.FaceId = 353
End With
End If
On Error Resume Next
Dim C As Comment
Set C = ActiveCell.Comment
If Not C Is Nothing Then
Set NewItem = Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, temporary:=True)
NewItem.Caption = "resize comment"
NewItem.OnAction = "autofitcomments"
NewItem.FaceId = 687
NewItem.BeginGroup = True
End If
End Sub

[lior03]

lello
what about cases when a cell has comment but no formula.
i do not see the resize comment shortcut.why?.

[Bob Phillips]

Because you have this line in your code



If IsEmpty(ActiveCell) Then Exit Sub

which exits if the cell is empty (comments will not affect this). Remove this line if you want.