Private Textbox Exit Sub question...

[Hendy88]

Hello all,

I have a Private Exit Sub for a Textbox that forces a person to enter
information in this box FIRST, before clicking into other parts of the
form. Basically, it's an employee time tracking form, and I've done
it this way because after they enter their employee number to start
off, it then goes up against another spreadsheet to bring back how
many vacation hours they have accrued to use up for the year. So this
way, they have to enter their number first in order to show what they
have, otherwise they have no business clicking around in other fields
and yada yada yada

Anyway, the question/problem I have is this: Once the userform is
displayed, I have part of the "verification" checking to see if they
left that textbox blank, and if they did it's shows a MsgBox that
says,

"Employee number field cannot be left blank. Please enter a valid
number."

Which works just fine, but if they click the "X" in the corner of the
userform to exit, it closes the form (which is what it's supposed to
do), but then because they left the textbox blank, after the forms
closes it still shows the message box telling them that the employee
field cannot be left blank.

Is there a way in the Private Exit Sub to specify, that IF the person
is "exiting" the textbox by clicking on the "X" to close, then not to
display the MsgBox?

Something like:

Private Sub EmpNum_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If EmpNum.Value = "" AND (Close Button is Clicked) Then
(Close UserForm without MsgBox)
Else
MsgBox "Employee number field cannot be left blank. Please
enter a valid number."
End If

End Sub

 

[Bob Phillips]

Trap that exit using QueryClose and set a variable

Option Explicit

Private fExitQuietly As Boolean
Private Sub EmpNum_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If Not fExitQuietly Then
If EmpNum.Value = "" Then
MsgBox "Employee number field cannot be left blank." & vbNewLine
& _
"Please enter a valid number."
End If
End If

End Sub

Private Sub UserForm_ACtivate()
fExitQuietly = False
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then
fExitQuietly = True
End If
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Hendy88]

That worked GREAT Bob thanks a million!!!

 

[Bob Phillips]

Just a little aside, tidier code would be (IMO)

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
fExitQuietlyCloseMode = 0
End Sub

You won't need to bother setting it in the activate event then either.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

Another option would be to use a label on the form instead of the msgbox.

Then you could populate that label (red font??) when there's an error -- or
clear it when it's ok.

And I bet no one would notice the error flashing by when the userform closes.

 

[Bob Phillips]

Dave, that is so kludgy I am surprised at you <g>


Bob

 

[Dave Peterson]

But I think it looks nicer than a msgbox when there really is an error.

So maybe a combination of your flag that stops the label from being updated
would be less of a kludge--but way more work <vbg>.

 

[Bob Phillips]

I agree it looks nicer, but MsgBox does have an advantage, it slaps you
awake!

 

[Dave Peterson]

If "cancel = true" is part of that _exit routine, it maybe irritating enough to
look up a bit and see the error message.

Maybe add some sounds and blinking text????

I agree it looks nicer, but MsgBox does have an advantage, it slaps you
awake!

 

[Hendy88]

Well to add to this... after what we talked about above is all said
and done... I then have a little "GIF Icon" they click where they can
actually save as [their Employee Number] to My Documents. The code I
have is this:

Private Sub SaveMYTIME_Click()
Dim FileName As String
Dim SaveFileTo As Variant

FileName = Range("C10").Value

SaveFileTo = Application.GetSaveAsFilename("C:\My Documents\" &
FileName, _
"Workbook (*.xls), *.xls", , "Save File As:")
End Sub


The [C10] Range is where the Textbox "dumps" their employee number
after they enter it. Here's the next question:

The "Save File As" Window comes up just fine, it automatically put
their Employee Number as the file name, and then has two buttons, Save
and Cancel, for them to proceed. I would like to know how to Close
the workbook IF they click Save, or return focus to the Workbook IF
they click Cancel out of that window.

How would I go about do that one since that window is an
Application.Window not a created form (if I said all that right)?

So I'm looking for something like:

SaveFileTo = Application.GetSaveAsFilename("C:\My Documents\" &
FileName, _
"Workbook (*.xls), *.xls", , "Save File As:")
If (Clicked Save) Then
ActiveWorkbook.Close
Else If (Clicked Cancel) Then
MyTIMEForm.SetFocus
End If

 

[Dave Peterson]

First, application.getsaveasfilename won't save the file. It can only get the
name (or false if the user hit cancel).

Dim SaveFileTo as Variant 'could be false, too

SaveFileTo = Application.GetSaveAsFilename("C:\My Documents\" & FileName, _
"Workbook (*.xls), *.xls", , "Save File As:")

if savefileto = false then
mytimeform.setfocus
else
application.displayalerts = false
activeworkbook.saveas filename:=savefileto, fileformat:=xlworkbooknormal
application.displayalerts = true
activeworkbook.close savechanges:=false
'clean up your form
'and go whereever you want.
end if

This is in a separate addin, right.

If it's in the same workbook that you're closing, then as soon as you close the
workbook with the code, nothing much is gonna happen.

Well to add to this... after what we talked about above is all said
and done... I then have a little "GIF Icon" they click where they can
actually save as [their Employee Number] to My Documents. The code I
have is this:

Private Sub SaveMYTIME_Click()
Dim FileName As String
Dim SaveFileTo As Variant

FileName = Range("C10").Value

SaveFileTo = Application.GetSaveAsFilename("C:\My Documents\" &
FileName, _
"Workbook (*.xls), *.xls", , "Save File As:")
End Sub

The [C10] Range is where the Textbox "dumps" their employee number
after they enter it. Here's the next question:

The "Save File As" Window comes up just fine, it automatically put
their Employee Number as the file name, and then has two buttons, Save
and Cancel, for them to proceed. I would like to know how to Close
the workbook IF they click Save, or return focus to the Workbook IF
they click Cancel out of that window.

How would I go about do that one since that window is an
Application.Window not a created form (if I said all that right)?

So I'm looking for something like:

SaveFileTo = Application.GetSaveAsFilename("C:\My Documents\" &
FileName, _
"Workbook (*.xls), *.xls", , "Save File As:")
If (Clicked Save) Then
ActiveWorkbook.Close
Else If (Clicked Cancel) Then
MyTIMEForm.SetFocus
End If

 

[Hendy88]

Dave,

It seems to work great except it errors out when I use .SetFocus. If
I try and click on "Cancel" in the Save As" window, the macro stops,
it says "Method or data member not found", and then highlights:

MyTIMEForm.SetFocus

in the VB code.

 

[Dave Peterson]

I thought that mytimeform was a control on that userform. Try using any control
on that userform.

 

[Hendy88]

No, MyTIMEForm is the actually name of the UserForm itself, but right
after I wrote that last post, I realized that all I had to do was
"focus" on any control (which is what you suggested). Thanks Dave,
and Bob, for your expertise in helping me get this resolved!

 

[Dave Peterson]

I saw myTimeForm.setfocus in an earlier post and was confused enough to think it
was a control <bg>.

But glad you and I came to the same conclusion <vvbg>.