Context Menu 2007/2010

[Guest]

(e-mail address removed) used his keyboard to write :

Ok, to send variable values from a button click, you need to have a
button for every possible variable. I strongly suggest you DO NOT make
a menuitem for each possibility.

It sounds to me like you need to do some validation of the ActiveCell
at the time the menuitem is selected. This should be done in an
'EntryPoint' procedure, where your decision as to what to do is
evaluated and passed to the appropriate procedure with args.

So.., I'm saying use a single entry point menuitem to execute a
procedure that does the logic part for all possibilities, and then
passes the instruction via args to your processing sub/function. Does
this make sense?

It does, but it's not where I'm headed.

http://tinyurl.com/66ah47u

In the picture of the menu the URL directs you to, if, say...Elmer
Fudd was to set the value of ActNum to 2, then run the macro, the
macro would then populate a base page with data.

That's where I'm headed.

So, in effect, the menuitem would need to perform two functions when
clicked...set the variable, and then call the procedure.

Is that possible?

 

[Guest]

It happens that (e-mail address removed) formulated :

Unfortunately .OnAction doesn't work that way. See my reply to your
next post. What you want is 1 menuitem that runs a proc that determines
what to do based on evaluation of the ActiveCell at the time the
menuitem is selected. That procedure would then pass args to your
process.

Example:
<Control>.OnAction = "EvaluateNextAction"

Sub EvaluateNextAction() 'EntryPoint
Dim Act As String, ActName as String, ActNum As Long
'Code to figure out values
'for each var goes here...

'Once the values are loaded, process them
Call ThisProcess Act, ActName, ActNum
End Sub

Sub ThisProcess(Act As String, ActName as String, ActNum As Long)
'Do your stuff
End Sub

I see where you're going with this, but in this case the cell isn't
active on the click event...just referenced with Set CurrentCell.

I appreciate your time and effort with this GS...but I think I'll just
create the procedures I'll require for now.

Thanks.

 

[GS]

So.., I'm saying use a single entry point menuitem to execute a

It does, but it's not where I'm headed.

http://tinyurl.com/66ah47u

In the picture of the menu the URL directs you to, if, say...Elmer
Fudd was to set the value of ActNum to 2, then run the macro, the
macro would then populate a base page with data.

That's where I'm headed.

So, in effect, the menuitem would need to perform two functions when
clicked...set the variable, and then call the procedure.

Is that possible?

I saw where you're headed; -not recommending you continue that path!

Menuitems have 2 properties you can store values in: 'Tag' and
'Parameter'. Both these store string values by default, so any numeric
values will have to be converted with a function like CLng(), for
example. Excel will know which menuitem was selected.

So.., in your procedure you need to get the property value stored and
then run with it...

<Control>.OnAction = "MyProcess"

Sub MyProcess()
Dim myValue As String, myNum As Long
myValue = CommandBars.ActionControl.Tag
'or...
myNum = CLng(CommandBars.ActionControl.Tag)
'Do stuff based on myValue or myNum
End Sub
===

Optionally...

Choice1:
You could implement a VB[A] function named "CallByName" whereby you can
run a method and pass any args you want, but this would require putting
the procs inside a class so they are Public methods of the class. IMO,
this is way too complicated for what you're trying to do.

Given what you explained so far, I would use a single menuitem and let
the entry point procedure (or VBA) decide what to do.

Choice2:
Have a single menuitem display a userform with a ListBox that users can
scroll to select actions. Use the list item's Index property to
identify which item was clicked, and pass a parameter based on that.
The names can be read into the list from a worksheet, and any other
values you want to use can be put into additional columns of the
ListBox. (not all cols need be visible if all you want to show is a
list of names)

 

[Guest]

If I haven't said Thank You, I have now.

<Control>.OnAction = "MyProcess"

Sub MyProcess()
Dim myValue As String, myNum As Long
myValue = CommandBars.ActionControl.Tag
'or...
myNum = CLng(CommandBars.ActionControl.Tag)
'Do stuff based on myValue or myNum
End Sub
===

This just eliminated over 100 lines of code in 25 separate
routines...in fact eliminated 24 routines...poof! Sianara!

I'm sure you have your reasons that you find this method unacceptable,
and after a dozen years messing with excel vba for entertainment, it
never ceases to amaze me how many alternate and more simple approaches
there can be, but for me this is a thrilling revelation.

I just bought you a cyber beer.

I will be using it as such, and I cannot thank you enough for
simplifying my code.

 

[GS]

(e-mail address removed) presented the following explanation :

If I haven't said Thank You, I have now.

This just eliminated over 100 lines of code in 25 separate
routines...in fact eliminated 24 routines...poof! Sianara!

I'm sure you have your reasons that you find this method unacceptable,
and after a dozen years messing with excel vba for entertainment, it
never ceases to amaze me how many alternate and more simple approaches
there can be, but for me this is a thrilling revelation.

I just bought you a cyber beer.

I will be using it as such, and I cannot thank you enough for
simplifying my code.

Glad I could help!

<FWIW>
I just remembered a project where I did something similar in a popup
menu, whereby I used a dropdown (msoControlDropdown) under a flyout
menuitem (msoControlPopup). I used the Index property to determine what
action to take based on the user's selection. This is similar to
holding a value in the Tag or Parameter props of multiple menuitems but
just takes up the space of a single item. I'm thinking you might
benefit having a shorter menu by replacing the list under each flyout
with a dropdown control. The same methodology is used to determine
which item was selected, just accessing different props:

Example:
<Control>.OnAction = "MyProcess"

Sub MyProcess
Dim vVal As Variant
vVal = CommandBars.ActionControl.List.ListIndex
If vVal = "Choose..." Then Beep: Exit Sub
'Do stuff based on vVal
End Sub

The dropdown was populated from a worksheet list using a For Each loop
that iterated each cell in the wks list range. The first item in the
list was "Choose...", which provided a means to exit the sub if that
was still the value when clicked. This means the rest of the list items
indexes started at 1. Arg values were stored in adjacent cells to the
list and so retrieving them was a simple task.

Optionally, you could store the list (and/or any parameters) in an
array.