How can I get gotFocus events from a user form?

[arnoldw]

I have user form in a macro in Excel. I want the user form to generate gotFocus events when it gets focus. Is this possible?

 

[nickdel]

arnold, when a form opens it has focus. There is no onFocus event specifically for a form.

If you can explain what you are trying to do maybe we can offer some alternative.

Nick

 

[pinkgecko]

You can use the Enter/Exit events for controls on a user form, which are basically the same as gotfocus/lostfocus. There is no focus for the userform itself, but it still has the activate event.

[blue]When birds fly in the correct formation, they need only exert half the effort. Even in nature, teamwork results in collective laziness.[/blue]

 

[arnoldw]

Thanks for your replies.

----------------------------------------------------------
nickdel:
I have a user form with a textbox (among other components) in Excel. The user form is modeless so the user can switch between accessing the Excel sheet and the user form. Whenever the user form is focused (or maybe it's called "activated") I want the caret to automatically blink in the textbox. Thus, I'm looking for a workaround for the following:

Private Sub UserForm_gotFocus()
TextBox1.SetFocus
End Sub

----------------------------------------------------------
pinkgecko:
I am not able to get your suggestion to work. I tried with a combobox like this:

Private Sub MyComboBox_Enter()
MsgBox ("Form entered")
End Sub

This is only executed when I specifically clicked on the combobox, it is not enought to click anywhere on the user form.

----------------------------------------------------------
I have tried to put in click and double click event handlers (XXX_Click and XXX_DblClick(ByVal Cancel As MSForms.ReturnBoolean)) for all my components on the user form and the user form itself and it works somewhat. It's not perfect because if the user bring the user form into focus by clicking on top the user form (on the blue title area), then no event is generated. It's not a particularly beautiful solution either because I end up with tons of identical event handlers. Is there a more elegant solution to my problem?

 

[nickdel]

Arnold, this is an interesting problem for which I do not know a solution! Let me have a think...

Nick

 

[ClulessChris]

would this not do?

Private Sub UserForm_gotFocus()
    TextBox1.SetFocus
    msgbox "Form entered"
End Sub

Don't count the days, Make the days count.

 

[arnoldw]

No, UserForm_gotFocus() does not exist, I just used it in my previous post to easily explain what I needed. If the _gotFocus() sub routine existed, I wouldn't have started this thread.

 

[ClulessChris]

appologies.
what i mean to say is write a procedure that mat be used to call/ load / show the user form. this routine would also perform all other required operations.

Sub CallUserForm1
    UserForm1.Show
    ' Do other stuff
end Sub

then each time the form is required to call this proc.

Don't count the days, Make the days count.

 

[arnoldw]

Thanks for your suggestions. However, I would prefer to let the user freely switch between accessing the Excel sheet directly and accessing the user form, without having to push any buttons. Whenever the user wants to switch from accessing the Excel sheet to accessing the user form, he/she should only have to click on the user form to activate/set focus on it. Ultimately that's what I would like to accomplish.

 

[ClulessChris]

The user form will have an on_Click event and the controls a got focus. Maybe these event could be used to trigger your routine?

Don't count the days, Make the days count.

 

[arnoldw]

Please see the lower part of my 28 Nov 07 4:13 post. If nobody comes up with a better suggestion, then I will use the appropriate events generated by all components on my form instead.