Similar the reference thread title, I need to know what control caused a
macro to run. Because of how my code is structured, it would be best if
I could know either one of 2 things:
- Does a macro "know" its own name? If so, I can get the name of the
control from the name of the macro since it's an Event of that very
control.

- Does a macro "know" what control/event caused it to run? This would
be more direct than parsing a macro name for the control name. If it
could just tell me its name!

In the "Need to know..." thread the thought of nesting a GotFocus event
trap could work but if I could more directly find out from a Property or
somehow it would be more convenient.

Thanks.


--
kbuilta
------------------------------------------------------------------------
kbuilta's Profile: http://www.excelforum.com/member.php...o&userid=32367
View this thread: http://www.excelforum.com/showthread...hreadid=521249

Macros won't know what caused them to run unless you tell them.

I Dim a string variable to store what-was-clicked information. For
instance, in the Click event handler for a command button I would do
something like:

Private Sub SomeButton_Click()
Caller="SomeButton"
DoSomething
End Sub

The Sub DoSomething would probably have a Select Case statement that
would test the variable Caller to determine what caused it to run.

You can use an integer variable as well. It's just easier to tell what
happened if you see "OKButton" rather than, say, "13".

In short, whenever you call a macro, tell your Caller variable what
made you call the code.

If you're trying to identify which control's OnAction fired the macro, try
looking at the ActionControl property of Commandbars in VBA help. It returns
a reference to Class CommandBarControl which lists various properties related
to controls. You can query these to know which control fired the macro
something like this:

If Commandbars.ActionControl.Tag = "ThisId" then...

where "ThisId" could be anything descriptive that relates to the control.
You assign the value to the control when it's created (usually). Also, you
could substitute the Tag property for any suitable one in the list. Typically
used ones besides Tag are Parameter and Caption, for example.

If more than one control fires the macro, you can use Select Case to take
the appropriate action based on the value of whatever property or properties
you implement.


If you're looking to identify a procedure that called the macro, you need to
pass a variable to it that identifies the procedure as the "Caller". To use
this method, you would set an argument for the macro something like this:

Sub MyMacro(Optional szCaller as String)

where the line in the calling procedure would be:

MyMacro sSource

where sSource is a string containing the name of the calling procedure. This
is used mostly for error handling, but can be utilized for anything
appropriate. It's declared in that procedure as follows:

Const sSource as String = "MyProcedure()"

Then you can query the argument's value to determine which action to take.
If several procedures call the macro and you need to know which one because
of special requirements for any/each caller, you can do something like this:

Select Case szCaller
Case = "MyProcedure()"
'do this...
Case = "OtherProcedure()"
'do this...
Case Else
'do this...
End Select

I hope this is helpful!
Regards,
GS

Thanks for the suggestions, I'll take a look. I already do some of the
variable passing in other cases to identify what made a call. To
illustrate, the case I'm working is an Estimating Sheet/Order Form for
a configurable product. The salesman can design up to 6 to 10 different
versions of most of the sections so each of those parameter sets is
repeated 6 to 10 times. The project is made up of mostly ComboBoxes on
the face of a worksheet and the salesman picks parameters through the
ComboBoxes.

I've already set it up so that error checking routines that are common
to many of these parameters are run through a single macro that is
called by Change events from the many ComboBoxes which each send
variables to the central error checker identifying which Box they are.

I'm expanding some new sections and was looking for a better way than
doing something like the following which I already have hundreds of
copies of with minor variations between them:

Sub KSH500_Design1_Material_Change()
FixtureType="KSH500"
DesignNum=1
Call Material_Check(FixtureType, DesignNum)
End Sub

somewhere else in the project is:
Sub Material_Check(FixtureType, DesignNum)
code to check stuff
etc...
End Sub

Or a variation on this is to change the BackColor of a ComboBox based
on its Value:

Private Sub KSH500_Design1_Orientation_Change()
BoxName = "KSH500_Design1_Orientation"
OrderSheet = "ReVive 500 & 600"
Call ComboBoxColor(OrderSheet, BoxName)
End Sub

which calls:
Sub ComboBoxColor(SheetName, BoxName)
On Error GoTo ErrorHandler
temp = Sheets(SheetName).OLEObjects(BoxName).Object.Value
If IsEmpty(temp) Or IsNull(temp) Then
Sheets(SheetName).OLEObjects(BoxName).Object.BackC olor =
RGB(256, 0, 0)
Else
Sheets(SheetName).OLEObjects(BoxName).Object.BackC olor =
RGB(192, 255, 192)
End If
ErrorHandler:
End Sub

The problem with this is I have, like I said above, hundreds of
ComboBoxes that make use of those checking macros so the short Change
macro is repeated over and over and I have to manually edit the
FixtureType variable value and the DesignNum value to match the
ComboBox name.

I was hoping to find some really, really easy way to get the macro to
tell me its own name and I could parse the control's name from it or
find out the calling control somehow since then I would have the name
directly. Then, even though I would still have hundreds of Change
macros, at least the code in them would be identical and I could just
copy it without having to edit each one and go blind in the process.
I'll look at the suggestions about trying to get the calling control's
identfication but does anyone know how to get a macro to refer to its
own name?

Thanks.


--
kbuilta
------------------------------------------------------------------------
kbuilta's Profile: http://www.excelforum.com/member.php...o&userid=32367
View this thread: http://www.excelforum.com/showthread...hreadid=521249

Hi kbuilta,

This sounds like a really complex estimate/order form. I have a similar
add-in project that has (currently) 17 product sheets (templates). Each one
has multiple configuration possibilities, so a salesperson can just enter a
quantity for each feature and formulas populate the necessary cells. These
sheets can contain hundreds of rows of line item info (specs), but the user
can hide (filter) any rows that don't have input. The sheets are protected,
with all input cells unlocked so the user navigates a logical path using the
Tab or Arrow keys. Conditional Formatting is used throughout.

It's incredibly easier than having all those controls on a sheet. In fact,
there's no controls at all (no macros), so the quotes can be sent anywhere
because they can be created in any workbook. It was designed this way so the
finished form could be emailed easily without firewall hassles. Everything
needed to process each sheet is in the add-in.

The nice thing about this design is there's no redundancy. Every feature for
each product is listed only once, in the section appropriate for its
application. Additional info is provided using cell comments where
appropriate. Updating is done by looping through each sheet by item number,
and looking it up on a query sheet to get description or pricing changes.

I originally had controls on the sheets, but I couldn't stand having them
there. I wanted a clean UI, and I couldn't make sense of the maintenance
required to manage them all (like you are facing now).

Food for thought! HTH
Regards,
Garry

Great input, Garry. I'm fitting this order sheet system into another,
larger set of worksheets that estimates a lot more than my part. I've
been talking with the guy who built that monstrosity and he agrees it
would be good to start over and do it all again with different going in
plans but there may be too much water under the bridge already (heh,
when is that ever not the case!).

Best Regards,
Kevin


--
kbuilta
------------------------------------------------------------------------
kbuilta's Profile: http://www.excelforum.com/member.php...o&userid=32367
View this thread: http://www.excelforum.com/showthread...hreadid=521249