Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Private Textbox Exit Sub question...
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Private Textbox Exit Sub question...
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) wrote in message s.com... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Private Textbox Exit Sub question...
That worked GREAT Bob thanks a million!!!
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Private Textbox Exit Sub question...
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) wrote in message ups.com... That worked GREAT Bob thanks a million!!! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Private Textbox Exit Sub question...
Dave, that is so kludgy I am surprised at you <g
Bob "Dave Peterson" wrote in message ... 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. wrote: 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Private Textbox Exit Sub question...
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 wrote: Dave, that is so kludgy I am surprised at you <g Bob "Dave Peterson" wrote in message ... 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. wrote: 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 -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Private Textbox Exit Sub question...
I agree it looks nicer, but MsgBox does have an advantage, it slaps you
awake! "Dave Peterson" wrote in message ... 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 wrote: Dave, that is so kludgy I am surprised at you <g Bob "Dave Peterson" wrote in message ... 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. wrote: 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 -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Private Textbox Exit Sub question...
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???? Bob Phillips wrote: I agree it looks nicer, but MsgBox does have an advantage, it slaps you awake! "Dave Peterson" wrote in message ... 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 wrote: Dave, that is so kludgy I am surprised at you <g Bob "Dave Peterson" wrote in message ... 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. wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Private Textbox Exit Sub question...
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Private Textbox Exit Sub question...
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. wrote: 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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Private Textbox Exit Sub question...
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. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Private Textbox Exit Sub question...
I thought that mytimeform was a control on that userform. Try using any control
on that userform. wrote: 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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Private Textbox Exit Sub question...
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! |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Private Textbox Exit Sub question...
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. wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Textbox question | Excel Discussion (Misc queries) | |||
Textbox question | Excel Discussion (Misc queries) | |||
Textbox question? | Excel Discussion (Misc queries) | |||
Private sub | New Users to Excel | |||
UserForm TextBox/ComboBox question | Excel Discussion (Misc queries) |