locked
Macro - What is the Name of the ActiveControl? RRS feed

  • 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?


    Wednesday, August 24, 2016 1:39 PM

Answers

  • 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:

    Command Button Embedded Macro

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

    UnLoad Embedded Macro

    Hope it helps...

    Wednesday, August 24, 2016 3:53 PM

All replies

  • 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

    Wednesday, August 24, 2016 1:45 PM
  • 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?


    Wednesday, August 24, 2016 1:48 PM
  • 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 

    Wednesday, August 24, 2016 2:25 PM
  • If you already know VBA, why do you restrict yourself to macros? Use VBA and go for mklaey's solution.

    Best regards, George

    Wednesday, August 24, 2016 3:17 PM
  • 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:

    Command Button Embedded Macro

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

    UnLoad Embedded Macro

    Hope it helps...

    Wednesday, August 24, 2016 3:53 PM
  • 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?

    Wednesday, August 24, 2016 3:54 PM
  • 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?

    Wednesday, August 24, 2016 3:56 PM
  • 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!
    Wednesday, August 24, 2016 3:57 PM
  • 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.
    Wednesday, August 24, 2016 3:58 PM