Is there a way to find out which control will have the next focus in VBA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

We are using VBA of a Microsoft Dynamics apps, but for the intention of displaying what we want, we will use the VBA of Microsoft apps Outlook (see below):

Instead of ' _AfterLostFocus()', Outlook uses '_Exit()' as seen in the pix.

- When the user clicks Y the event "_Exit()" is fired.
- Since the user clicked Y, this event was fired and a series of condition is executed
- If a condition is met, then the userForm1 is unload
- But, if we can detect in this event the "NEXT" field, that is "Y" was clicked, we don't want to unload

Is there a way to now the next field the user clicked?
(since the same clicked fires the "_exit()" event, we think that the VBA can give us this info)

Hope we expressed it this time.

I think I understand and thus I think you need to change your thinking, Instead of asking the old control why did you loose focus, you need to ask the new control, who had focus last.

ASKER

Yes, but if the condition is met within the old control (X field), then the apps is unloaded. We want not to unload if the next control is Y.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

In the example we gave 2 field, but our form has over 45 fields.

That would work if there only 2 fields.

We want is if when the focus is in X, the user clicks Y, then it not unload, but if the user clicks Z, A, C, then it will continue and download.

We want to place in that routine you gave us a command that would know that X is clicked since the focus is about to exit. Something like this:

Private Sub x_Exit(ByVal Cancel As MSForms.ReturnBoolean)
      If Focus_Clicked_after_this_routine_is_NOT_ Y Then
          If condition_statements = True Then 
             UnLoad            
         EndIF
End Sub

Open in new window

Hope this helps.

We want is if when the focus is in X, the user clicks Y, then it not unload

Once the focus leaves X you can't run any code that's in X. You can only run code that's in the control that has the focus. So what you need to do is to tell your code that you have been in X using this code that I posted above...

Private Sub x_Exit(ByVal Cancel As MSForms.ReturnBoolean)
mstrLast = "x"
End Sub

Open in new window

... and in Y, check to see if mstrLast is "X". If so then don't unload but rather just change mstrLast to "".

ASKER

Yes we understand after leaving X no codes will be run. We would like to know, the user actually will clicking Y, if prior leaving we can determine the field the user clicked out to.

Can this be done?

VBA or I assume any language can not predict the future. It can only (with help) record the past and act on it.

ASKER

Actually, we are not looking to predict. Lets see how can we explain this.

Ok, the only reason why the "_exit()" control or event has been triggered or fire is because the user clicked on another field. That said, we went in to assume since the user is actually clicking the next field, maybe VBA would have access or monitor all the clicking ...

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Sorry for the delay just got back ... ok will check...

ASKER

Hi,

in your excel, it's knowing the LAST place we were. We want to know what button was clicked prior leaving X button (in this case "Y").

More info:

Using your excel (see pix below),

What we are looking for is, is there a command that would tells us the Y button was clicked.

In other words, if we can place a command in "Private Sub x_Exit" routine that will tell us what button was clicked prior exiting.

Hope we have given more info to our need.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Ok.

We asked this question since Excel is develop[ed in C++. That said, we thought that there may be some sort of command that would that.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

When you click another control no other event fires before the Exit event, because leaving the control fires the Exit event.

ASKER

Yes.

So besided the actual events, control & focus we have indicated, is there any other way of executing what we want? (X knows that Y was pressed)

As proof I added a Debug.Print statement in every event available to x and here's the result.
x_MouseMove
x_MouseMove
x_MouseMove
x_MouseMove
x_MouseMove
x_MouseMove
x_MouseMove
x_MouseMove
x_MouseMove
x_MouseMove
x_MouseMove
x_MouseMove
x_MouseMove
x_MouseMove
x_MouseMove
x_MouseMove
x_Enter
x_MouseDown
x_MouseUp
x_Click
x_MouseMove
x_MouseMove
x_MouseMove
x_MouseMove
x_MouseMove
x_MouseMove
x_MouseMove
x_MouseMove
x_MouseMove
x_MouseMove
x_MouseMove
x_MouseMove
x_MouseMove
x_MouseMove
x_Exit
y_Enter
y_MouseDown

So besided the actual events, control & focus we have indicated, is there any other way of executing what we want? (X knows that Y was pressed)

No, only (Y knows that X was the last control clicked) is possible.

If this was really something that I needed to achieve I would be looking at setting up a wndproc and looking at the messages for the form.

But I assume that as the Event order is as it is, that the message of the window itself are almost identical.

So rayluvs, let me ask again what I asked above which was

why can't you run a procedure in y that does what you wanted to do in x?

ASKER

You solution should work 100%, but the apps has over 45 fields, which means in order to Y to work effectively, we would have to place mstrLast = "x" in all 45+ fields (this is because the user can click on any of these fields at any time).

Since this is a large super long coding, we wanted to see if we can detect the actual "click" of "Y" from within "X".

we would have to place mstrLast = "x" in all 45+ fields

I'm sure you can do that in under 5 minutes.

ASKER

Yes of course.

So to close the question, then that is the only way to go about this specific need?

Yes.

ASKER

Ok thanx. You guys have been very helpful.

Please don't forget to choose the Best Solution and optionally any Assisted Solution(s).

ASKER

Just a thought.

The reason we placed the question, we figured that since Excel is written in c++, maybe there was an instruction hidden deep within the code that would have had the feature we were looking for.

again, thanx.