About Us

GS[_2_]

presented the following explanation :
On Tue, 25 Jan 2011 21:11:33 -0500, GS wrote:

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.

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.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
2007 Compatibility Pack -- Save As context menu	AndyE46	Excel Discussion (Misc queries)	1	December 11th 09 05:28 PM
How to get Ribbon Controls on Context menu in Office Excel 2007	sang	Excel Programming	1	January 28th 09 08:23 PM
Adding commands to the Excel 2007 Chart context menu	Andreas	Charts and Charting in Excel	1	January 23rd 09 08:15 PM
Custom Context menu (Right click menu) not working in sheet changeevent.	Madiya	Excel Programming	3	February 11th 08 01:24 PM
Adding context menu to shapes in Excel 2007	JJ	Excel Programming	0	June 2nd 07 12:28 PM

Menu

About Us