ExcelBanter - View Single Post

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