Form Control Calling Macro

[pcstru]

I have a bunch of form controls all calling the same macro by specifying it as the "Run Macro on Exit" property. How can the macro determine which control has called it?

[Kafrin]

Add a parameter to your macro that holds the control or the control's name (depending on what you want to use it for). Then in each place that you call the macro you need to add the parameter that identifies the control.

If you want to be able to call it when you don't care where it's from then make the parameter optional.

To help more specifically I'd need to know why you need to know where the macro is called from, what are you going to do with this information?

[pcstru]

To help more specifically I'd need to know why you need to know where the macro is called from, what are you going to do with this information?

Thanks. Unfortunately the "run Macro on Entry/Exit" dropdowns on the control properties doesn't let me select any macro's which have parameters. What I have is a table with many rows (y) and columns (x). The top row (of any pair of rows) contains the drop downs and when they are filled in by a user I need to color the cell immediately below the cell with the dropdown (i.e. y+1,x ). Currently I'm just scanning all the controls on the form every time the macro is called on exit from the field - which seems a little inefficient. What I really want to pass to the sub is a reference to self, then, via the name property, I can immediately determine which control it is and which cell should be colored.

[geekgirlau]

If you can't select a macro with a parameter, what about creating a new macro. Your new macro would simply call the first macro and set the parameter, and your control would run the new macro on entry/exit.

[vba]
Sub MyNewMacro
MyRealMacro "ControlName"
End Sub
[/vba]

Clunky I know, but it will work.

[pcstru]

If you can't select a macro with a parameter, what about creating a new macro. Your new macro would simply call the first macro and set the parameter, and your control would run the new macro on entry/exit.

[vba]
Sub MyNewMacro
MyRealMacro "ControlName"
End Sub
[/vba]
Clunky I know, but it will work.

That would work, requires a macro for each dropdown and there will be ~100 dropdowns. Still, would be quicker than looping through each control. Thanks.

[macropod]

Hi pcstru,

Another way is to use an 'On Entry' macro attached to each of the formfields in question, coded along the lines of:

Option Explicit
Public FFName As String
 
Sub GetFFName()
FFName = Selection.FormFields(1).Name
End Sub

Your 'On Exit' macro can then retrieve the value of the 'FFName' variable. If you've got a lot of formfields to make this modification to, you could use a one-off piece of code to modify each formfield's properties ...

[fumei]

You may get unexpected results from that.

[fumei]

You may get unexpected results from that.

[macropod]

Hi Gerry,

I've never had any unexpected results from the approach I suggested. The only thing one needs to watch out for is that, if one of the fields concerned is the default when the document is opened, you need to use a Document_Open macro to ensure the FFName variable gets set.

Perhaps you could explain your response?

[geekgirlau]

I don't know about getting unexpected results, but I'm slightly allergic to public variables - seen too much poorly written code with hundreds of them, making it very difficult to trace values.

[pcstru]

Hi pcstru,

Another way is to use an 'On Entry' macro attached to each of the formfields in question, coded along the lines of:

Option Explicit
Public FFName As String
 
Sub GetFFName()
FFName = Selection.FormFields(1).Name
End Sub

Your 'On Exit' macro can then retrieve the value of the 'FFName' variable. If you've got a lot of formfields to make this modification to, you could use a one-off piece of code to modify each formfield's properties ...

Thankyou! You have effectively answered the question I was asking - the "selection" reference is effectively "self" if used with care. I'll watch out for the first control being the default selection - since there should only be the dropdowns active (and calling the proc), then if the global is "" it must be the first control. Thanks again.