Macro - What is the Name of the ActiveControl?

Question

Hello, Community!

In Forum searching I couldn't find answer to this question.

In Macro for Unload Event, I want to know if User clicked the Exit button.

Reason:

If they clicked Exit button then will Open another Form.

Otherwise, will simply allow Unload to continue (i.e. let the Form Close).

I've tried comparing [Screen].[ActiveControl].[Name] to literal, but get OLE Object error.

I've tried setting TempVar to [Screen].[ActiveControl].[Name], and again get that same error.

My goal is simply like:

Condition:

[Screen].[ActiveControl].[Name] = "cmdHome"

---

If the directions say go straight, but I turn left, then right: Will I still get there?

Answer 1

The best way is to define a module level variable, say

  Dim ExitClicked As Boolean

Then in the Form_Open or _Load event, you set

  ExitClicked = False

In the cmdHome_Click event, you set

  ExitClicked = True

And finally you can test the value of ExitClicked in the Form_Unload event to decide what to do next.

Matthias Kläy, Kläy Computing AG

Answer 2

Thanks, Matthias.

But....I "know" how to in vba.

My question is regarding a Macro.

Apologies, Community!

I probably wasn't clear.

In vba:
Dim ctl As Control
Set ctl = Screen.ActiveControl
If ctl.Name = "ThisName" Then
    . ' Do something here.
ElseIf ctl.Name = "AnotherName" Then
    . ' Do something here.
End If

In Macro?
Condition
[Screen].[ActiveControl].[Name]="ThisName"
Action:
OpenForm

---

If the directions say go straight, but I turn left, then right: Will I still get there?

Answer 3

I don't think that you have access to the Screen object in a macro. But perhaps it is possible to do it the way I described above with the SetTempVar/SetLovalVar macro commands. I almost never work with macros, so somebody else may know more.

Matthias Kläy, Kläy Computing AG 

Answer 4

If you already know VBA, why do you restrict yourself to macros? Use VBA and go for mklaey's solution.

---

Best regards, George

Answer 5

Hi PQSC. I think the problem is the use of the UnLoad event. By the time it fires, the Screen Object is probably cleared already. If you insist on using macros, may I suggest the following approach instead. In the Click Event macro for the button, set a TempVar with the name of the button. For example, see the following image:

Then, in the UnLoad event of the form, check the value of the TempVar. For example, see the following image:

Hope it helps...

Answer 6

Thanks for the reply and an Excellent Question...

I "have" a generic Unload Event handler.

This is a simple DB where I didn't want to distribute powerful code.

The DB is for a very small office, where the Users are not very sophisticated.  All of their Forms will have a "Home" button and, of course, I leave the Form's Close Button available.

I wanted to be able to trap if the user "gracefully" exited a Form, or clicked the "Access" Close button.

I wanted to paste in a Macro into all of the Form's Unload Events which would call a Named Macro that would trap this.

Obviously, I don't do much by way of Access Macros.

Anyways, for those who may have the same needs I did, I found out where I was going wrong.

I couldn't inspect the Name of the ActiveControl from the Unload Event macro.

But, I could (as I should have suspected/known) access it from the Macro for the Home button's Click Event.

So, setting a TempVar in the button's Click Event macro enables inspection in the Unload Event macro.

---

If the directions say go straight, but I turn left, then right: Will I still get there?

Answer 7

Wow!  That was cool.

I "just" wrote the same answer.

You had it absolutely right.

Funny.   Thanks DBguy.....

---

If the directions say go straight, but I turn left, then right: Will I still get there?

Answer 8

Hi PQSC. I didn't know you needed this process for multiple forms, so I didn't suggest using Screen.ActiveControl in the button macro; but yes, you can use the Screen object in the Click event of the buttons. Cheers!

Answer 9

Wow!  That was cool.

I "just" wrote the same answer.

You had it absolutely right.

Funny.   Thanks DBguy.....

---

If the directions say go straight, but I turn left, then right: Will I still get there?

Great minds think alike! Good luck with your project.