How to Activate procedure with parameters

[ivarsb]

In the chapter 4 ("Microsoft excel object") of Microsoft Office 97/
Visual Basic Programmer's guide I got sample code of adding extra
function for the right click menu.

I have a problem with Onaction property, when I tried also include
parameters for sub. I need to run sub with "Target" ( the cell on
which I clicked with right button) as parameter but VBA doesn't allow
to do that.


Also I wanted to know why the sample code set Control.Tag (icbc.Tag)
as brccm. For what the Tag property is used. And should I allways use
"brccm" as Tag value.



I am working on XL2000



This is the code I use:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel
As Boolean)


For Each icbc In Application.CommandBars("cell").Controls
If icbc.Tag = "brccm" Then icbc.Delete
Next icbc


If Not Application.Intersect(Target.Cells(1, 1), Range("A:A")) Is
Nothing Then
With Application.CommandBars("cell").Controls _
.Add(Type:=msoControlButton, before:=1, Temporary:=True)
.Caption = "Jauns instruments"
.OnAction = "mkrInstr"
.Tag = "brccm"
End With
End If




Thank you,
Ivars

 

[Tom Ogilvy]

The first thing your code does it delete any controls that have a tag
property of brccm. This is so it does not put multiple button on the menu
that are duplicates. It then creates your menu items and puts brccm in the
tag property so the next time the code runs, it will find this control and
delete it.

There is nothing special about the string "brccm". It is just what they
chose to use to mark their controls. You could use another string. As far
as passing an address, while it is possible in xl97, this undocumented
approach is not supported in some of the latest versions, so it would be
better to use another approach. One approach you might use is to put the
address of the cell in the tag along with the brccm. Another approach is to
just use ActiveCell.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel
As Boolean)


For Each icbc In Application.CommandBars("cell").Controls
If Left(icbc.Tag,5) = "brccm" Then icbc.Delete
Next icbc



If Not Application.Intersect(Target.Cells(1, 1), Range("A:A")) Is
Nothing Then
With Application.CommandBars("cell").Controls _
.Add(Type:=msoControlButton, before:=1, Temporary:=True)
.Caption = "Jauns instruments"
.OnAction = "mkrInstr"
.Tag = "brccm" & Target.Parent.name & "!" Target.Address(0,0)
End With

End If

End Sub

Sub mkrInstr()
Dim sStr as String, Target as Range
sStr = CommandBars.ActionControl.Tag
sStr = Mid(sStr,6,255)
set Target = Range(sStr)
msgbox Target.Address(external:=True)

End Sub

 

[Wassim]

Hello Ivars

When you say: <<< How to Activate procedure with parameters >>>

And then you say: <<< I need to run sub with "Target" ( the cell on
which I clicked with right button) >>>

Are you trying to pass the address of the cell you right-clicked on to
a procedure that would run from the Context-Sensitive Menu

or

From the Mouse Click?

I would suggest that you an use Activecell.Address to grab the cell
where the Right-Mouse click happened. Try it and reply with more
questions.

As to the Tag field, this is a User field, that is its for the
programmer to use and it has nothing to do with the object itself.

The tag field is very helpful when you want to logically group
objects.

say you want to change all CheckBoxes values, but you want to keep one
default.

If you use For Each CbCheckBox in Me.Checkboxes you will affect all
checkboxes.

But if you set the default checkbox's tag to NoChange then you can
use:

For Each CbCheckBox in Me.Checkboxes
If CbCheckBox.Tag <> "NoChange" Then
'/Code here to do what you want to do.
End If

Next CbCheckBox

Tag is a name so to speak.

Hope this Helps.

Wassim

 

[ivarsb]

Thank you for answering for my questions.

The answers cleared up how to use TAG property. And I find it very
useful. Now I am wondering is there similar property for cells. I mean
is there some kind of information storage place which can't be seen
easily by users, but which would available for code usage. I can use
for that comment boxes but that are messing up worksheets with corner
triangles.