Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get line no from right click menu ("Cell")
Hi NG
When I'm right clicking in a cell, I want my owen menu. I can do this. The menu depends on text in 5, 10 og 20 consecutive cells. I'm using the text. snip **** start Dim lCount As Long For lCount = 1 To lListCount Dim MyList MyList = data(lCount) '// text from cells Dim cBut As CommandBarButton Set cBut = .CommandBars("Cell").Controls.Add(Type:=msoControl Button, Temporary:=True) With cBut .Caption = MyList .Style = msoButtonIconAndCaption ' msoButtonCaption .FaceId = lCount + 50 .SetFocus .OnAction = "RunMe" .Tag = lCount End With snip **** end When I'm clicking on e.g 3th line in right click menu ("Cell"), how can I knew this, if I only have .OnAction = "RunMe" If .OnAction = "RunMe_" & lCount then I must have aboute 30 macros or trap error, because I do not have any macro, and read trapinfo or macro name and number. Perhaps and quite different approach? -- Best regards Jorgen Bondesen |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get line no from right click menu ("Cell")
Jorgen Bondesen brought next idea :
Hi NG When I'm right clicking in a cell, I want my owen menu. I can do this. The menu depends on text in 5, 10 og 20 consecutive cells. I'm using the text. When I'm clicking on e.g 3th line in right click menu ("Cell"), how can I knew this, if I only have .OnAction = "RunMe" If .OnAction = "RunMe_" & lCount then I must have aboute 30 macros or trap error, because I do not have any macro, and read trapinfo or macro name and number. Perhaps and quite different approach? Different approach! If all your test values are text then you can use a delimited string to check the contents of the cell right-click against using InStr(). This assumes the text entered is an element of an expected list. In a standard module declarations section: Public Const gsValidText As String = "Text1,Text2,Text3,Text4,Text5" I suggest you position your menu at the top of the popup so it's more readily available to your user, AND makes your managing of the menuitem in code a bit easier. **Note: The dot preceeding 'CommandBars' in your Set statement isn't necessary. (The CommandBars collection is one of the Application globals you can access without specifying 'Application.' as the object reference** You can use a single proc for the OnAction and just redirect code flow within that proc using a 'Select Case' construct that determines what code to execute based on the menuitem's Caption. <aircode Sub RunMe() Select Case CommandBars.ActionControl.Caption Case "Text1": Call Text1Proc Case "Text2": Call Text2Proc Case "Text3": Call Text3Proc 'and so on... End Select End Sub **Note that you could use separate procs for each text item and redirect to there from this entry point (as shown here) OR You could write the code under each 'Case' and run all from here.** You can set Caption/Visible props via the Worksheet_BeforeRightClick event. <aircode Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _ Cancel As Boolean) With CommandBars("Cell").Controls("RunMe") If InStr(1, gsValidText, Target.Value, vbTextCompare) 0 Then .Caption = Target.Value: .Visible = True Else .Caption = "RunMe": .Visible = False End If End With End Sub This will make the menuitem appear only if the cell content meets your criteria. To build the menuitem: In the Workbook_Open event: Private Sub Workbook_Open() Call AddMenus End Sub OR.. in a standard module: Sub Auto_Close() Call AddMenus End Sub Sub AddMenus() ' Delete the menu if it exists, then replace it Dim NewMenu As CommandBarControl On Error Resume Next With CommandBars("Cell").Controls(1) If InStr(1, gsValidText, .Caption, vbTextCompare) 0 _ Or .Caption = "RunMe" Then .Delete End With On Error GoTo 0 Set NewMenu = _ CommandBars("Cell").Controls.Add(Type:=msoControlB utton, _ Befo=1, Temporary:=True) With NewMenu .Caption = "RunMe": .OnAction = "RunMe": .Visible = False End With End Sub To remove the menu: In the Workbook_BeforeClose event: Private Sub Workbook_BeforeClose(Cancel As Boolean) CommandBars("Cell").Reset End Sub OR.. in a standard module: Sub Auto_Close() CommandBars("Cell").Reset End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get line no from right click menu ("Cell")
If you have several menu choices to add then I suggest using your own
custom popup menu to replace "Cell". This would be easier to manage than working with multiple menuitems added to "Cell". Let me know if this is a viable option for you... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get line no from right click menu ("Cell")
Hi Garry
Thanks, very usefull. -- Best regards Jorgen Bondesen "GS" skrev i en meddelelse ... If you have several menu choices to add then I suggest using your own custom popup menu to replace "Cell". This would be easier to manage than working with multiple menuitems added to "Cell". Let me know if this is a viable option for you... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get line no from right click menu ("Cell")
I'm not sure what you're doing where you'd need that many choices inside the
rightclick menu. Maybe you could use the activecell.row or the row with the number of rows in the first area of the selection???? But since you're using the .tag property, you could call the same RunMe procedure and just decide based on that .tag of the button you clicked. I don't know what data() does, so I just plopped in some text: Option Explicit Sub auto_open() Dim lCount As Long Dim MyList As Variant Dim lListCount As Long Dim cBut As CommandBarButton lListCount = 4 For lCount = 1 To lListCount MyList = "runme " & lCount Set cBut = Application.CommandBars("Cell").Controls _ .Add(Type:=msoControlButton, Temporary:=True) With cBut .Caption = MyList .Style = msoButtonIconAndCaption ' msoButtonCaption .FaceId = lCount + 50 '.SetFocus .OnAction = ThisWorkbook.Name & "!RunMe" .Tag = lCount End With Next lCount End Sub Sub RunMe() With Application.CommandBars.ActionControl 'MsgBox .Caption & vbLf & .Tag Select Case .Tag Case Is <= 2 MsgBox "it's small" Case Else MsgBox "it's not small" End Select End With End Sub On 02/28/2011 11:22, Jorgen Bondesen wrote: Hi NG When I'm right clicking in a cell, I want my owen menu. I can do this. The menu depends on text in 5, 10 og 20 consecutive cells. I'm using the text. snip **** start Dim lCount As Long For lCount = 1 To lListCount Dim MyList MyList = data(lCount) '// text from cells Dim cBut As CommandBarButton Set cBut = .CommandBars("Cell").Controls.Add(Type:=msoControl Button, Temporary:=True) With cBut .Caption = MyList .Style = msoButtonIconAndCaption ' msoButtonCaption .FaceId = lCount + 50 .SetFocus .OnAction = "RunMe" .Tag = lCount End With snip **** end When I'm clicking on e.g 3th line in right click menu ("Cell"), how can I knew this, if I only have .OnAction = "RunMe" If .OnAction = "RunMe_"& lCount then I must have aboute 30 macros or trap error, because I do not have any macro, and read trapinfo or macro name and number. Perhaps and quite different approach? -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get line no from right click menu ("Cell")
Hi Dave.
Thanks, very usefull. -- Best regards Jorgen Bondesen "Dave Peterson" skrev i en meddelelse ... I'm not sure what you're doing where you'd need that many choices inside the rightclick menu. Maybe you could use the activecell.row or the row with the number of rows in the first area of the selection???? But since you're using the .tag property, you could call the same RunMe procedure and just decide based on that .tag of the button you clicked. I don't know what data() does, so I just plopped in some text: Option Explicit Sub auto_open() Dim lCount As Long Dim MyList As Variant Dim lListCount As Long Dim cBut As CommandBarButton lListCount = 4 For lCount = 1 To lListCount MyList = "runme " & lCount Set cBut = Application.CommandBars("Cell").Controls _ .Add(Type:=msoControlButton, Temporary:=True) With cBut .Caption = MyList .Style = msoButtonIconAndCaption ' msoButtonCaption .FaceId = lCount + 50 '.SetFocus .OnAction = ThisWorkbook.Name & "!RunMe" .Tag = lCount End With Next lCount End Sub Sub RunMe() With Application.CommandBars.ActionControl 'MsgBox .Caption & vbLf & .Tag Select Case .Tag Case Is <= 2 MsgBox "it's small" Case Else MsgBox "it's not small" End Select End With End Sub On 02/28/2011 11:22, Jorgen Bondesen wrote: Hi NG When I'm right clicking in a cell, I want my owen menu. I can do this. The menu depends on text in 5, 10 og 20 consecutive cells. I'm using the text. snip **** start Dim lCount As Long For lCount = 1 To lListCount Dim MyList MyList = data(lCount) '// text from cells Dim cBut As CommandBarButton Set cBut = .CommandBars("Cell").Controls.Add(Type:=msoControl Button, Temporary:=True) With cBut .Caption = MyList .Style = msoButtonIconAndCaption ' msoButtonCaption .FaceId = lCount + 50 .SetFocus .OnAction = "RunMe" .Tag = lCount End With snip **** end When I'm clicking on e.g 3th line in right click menu ("Cell"), how can I knew this, if I only have .OnAction = "RunMe" If .OnAction = "RunMe_"& lCount then I must have aboute 30 macros or trap error, because I do not have any macro, and read trapinfo or macro name and number. Perhaps and quite different approach? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add "paste values" to right-click shortcut menu-how do? Excel 2010 | Excel Programming | |||
Add "paste values" to right-click shortcut menu-how do? | Excel Programming | |||
It's not CommandBars("Cell"), right-click menu! | Excel Programming | |||
save and restore "Workbook Menu Bar" & "Cell" menus | Excel Programming | |||
disable right mouse click on "Worksheet Menu Bar" | Excel Programming |