View Full Version : on condition rightclick
lior03
07-30-2006, 05:40 AM
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
07-30-2006, 06:37 AM
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
07-30-2006, 08:00 AM
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
07-30-2006, 08:23 AM
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
08-01-2006, 01:17 AM
dearest xld
i copy the code . why do i get a msgbox with error 91?
thanks
Bob Phillips
08-01-2006, 01:46 AM
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
08-14-2006, 12:44 AM
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
08-14-2006, 01:17 AM
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
08-14-2006, 01:29 AM
lello
what about cases when a cell has comment but no formula.
i do not see the resize comment shortcut.why?.
Bob Phillips
08-14-2006, 03:08 AM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.