Beginner - Excel - LostFocus() - Does it work ?

[Guest]

Hi,
I am unable to get the LoxtFocus() event to work in an Excel form.

I am now wondering if it actually works.

***************************************
***************************************
Private Sub txtParentFirstName_LostFocus()
If frmParent.txtParentFirstName.TextLength < 3 Then
Call proNameImputError
End Sub
***************************************
***************************************

Any advice is warmly welcomed

Kind regards
duBe

 

[Dave Peterson]

Take a look at the code window for your form.

At the top of that window, you'll see two dropdowns. If you let your cursor
linger over the left hand dropdown, you'll see a tooltip of "Object". Use the
dropdown to select TxtParentFirstName (I'm assuming it's a textbox).

If you let your cursor linger over the right hand dropdown, you'll see
"Procedure".

If you look at the dropdown options for that textbox, you won't see _lostfocus.
But you will see Exit.

By selecting the Exit procedure, you'll see that you get the correct procedure
name/syntax. This worked ok for me (I didn't know what ProNameImputError did,
so I just used a msgbox.

Option Explicit
Private Sub txtParentFirstName_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.txtParentFirstName.TextLength < 3 Then
MsgBox "Call proNameImputError"
Cancel = True
End If
End Sub

(By the way, it's iNput, not iMput.)

(and I used Me instead of frmParent. Me represents the object (userform this
time) that owns the code.)

 

[Guest]

Thanks Dave,
that worked a treat, although I don't think I really follow the logic of the
proceedure, but then again, it works, so I'm not complaining.

Option Explicit
Private Sub txtParentFirstName_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.txtParentFirstName.TextLength < 3 Then
MsgBox "Call proNameImputError"
Cancel = True
End If
End Sub

thanks for the English lesson, don't know what I was thinking, probably
wasn't.

Thanks again
duBe

 

[Guest]

Hi again,
I did some more work on my project & found an interesting (or not)
consequence of using the "exit" proceedure, that I hope someone could help me
resolve.

I have a command button "cmdCancel" that should unlad the UserForm.

In fact, it used to unlad the UserForm, but since inserting the "exit"
proceedure, the "cmdCancel" will not execute until the "if" statement for the
"txtParentFirstName" validation is satisfied.

Is there a work-around for this?

I would really like the "cmdCancel" to have priority over everything, not
wait for permission from everything else on the UserForm.

Kind regards
duBe

 

[Dave Peterson]

Maybe you could enable/disable the Ok button based on your validation checks.

(I didn't take the time to match your control names.)

Option Explicit
Private Sub cmdCancel_Click()
Unload Me
End Sub
Private Sub TextBox1_Change()
Call CheckCmdOk
End Sub
Private Sub TextBox2_Change()
Call CheckCmdOk
End Sub
Private Sub UserForm_Initialize()
Me.cmdCancel.Enabled = True
Me.cmdOK.Enabled = False
End Sub
Private Sub CheckCmdOk()
Dim okToContinue As Boolean
If Len(Me.TextBox1.Value) < 3 _
Or Len(Me.TextBox2.Value) < 4 Then
okToContinue = False
Else
okToContinue = True
End If
Me.cmdOK.Enabled = okToContinue
End Sub

 

[Guest]

Hey THANKS Dave,
I have learnt heaps from this, your code looks TOPS, soooo much better than
I ever could have conceived myself.

To be honest, my mind was not even on that same track to write it that way.

Cheers & Merry Christmas
duBe

 

[Dave Peterson]

Myrna Larson and Bill Manville wrote an addin that compares worksheets. You can
find it at:

http://www.cpearson.com/excel/whatsnew.htm
look for compare.xla

Their code is very nice. You may want to download it (and use the compare!),
but look at how they did things.