Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing incorrect data from being copied
I have a user form that has three text boxes along with an OK command and a
Cancel command. The first textbox is an employee ID number, the next two are dates. The Employee ID is entered and then a VLOOKUP formula goes to another worksheet and finds the employee name and other data. If it doesn't find a name I have written into the formula to enter "Invalid Employee Number" in the cell. Here is the situation: If an invalid employee number is entered and the user doesn't notice bofore clicking OK, the "invalid" data is copied to another hidden worksheet and it is just a sheet of errors since the employee number wasn't valid. OR If the user enters a valid number, but doesn't notice until he/she hits OK that the wrong employee number was entered, then that wrong employee data is copied to the hidden worksheet. What is the best way to prevent either of these from getting copied before the user is certain the correct employee number is entered? I attempted to create a MsgBox at the first Input box asking "Is this the Correct Employee Number?". However the message box appeared after a single digit was entered. For example if the employee number is 1554, the message box popped up when you type 1, not at the end of typing 1554. What do you experts suggest???? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing incorrect data from being copied
I'm not an expert but why don't you make the textbox for entering the ID
number into a combobox that selects from the list of employee numbers. That way the user doesn't have the opportunity to enter a wrong number. The combobox row source can be set in the property list or by VB. It can refer to the list of #'s and names on the other sheet and put the value on the sheet you are calling the userform from. Set BoundColumn to 1, ColumnCount to 2, ColumnWidths to something like 50, 120. Set the Rowsource to something like Sheet1!A1:B1000 and the Controlsource to something like Sheet2!C5. The value you select will be placed in C5. You should probably use VB so that the value is updated before closing the form. "TimN" wrote: I have a user form that has three text boxes along with an OK command and a Cancel command. The first textbox is an employee ID number, the next two are dates. The Employee ID is entered and then a VLOOKUP formula goes to another worksheet and finds the employee name and other data. If it doesn't find a name I have written into the formula to enter "Invalid Employee Number" in the cell. Here is the situation: If an invalid employee number is entered and the user doesn't notice bofore clicking OK, the "invalid" data is copied to another hidden worksheet and it is just a sheet of errors since the employee number wasn't valid. OR If the user enters a valid number, but doesn't notice until he/she hits OK that the wrong employee number was entered, then that wrong employee data is copied to the hidden worksheet. What is the best way to prevent either of these from getting copied before the user is certain the correct employee number is entered? I attempted to create a MsgBox at the first Input box asking "Is this the Correct Employee Number?". However the message box appeared after a single digit was entered. For example if the employee number is 1554, the message box popped up when you type 1, not at the end of typing 1554. What do you experts suggest???? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing incorrect data from being copied
I played with some code to put in the userform. Change names accordingly
Private Sub UserForm_Initialize() ComboBox1 = Range("C5") ' if the value exists when you open form TextBox1 = ComboBox1.Column(1) End Sub Private Sub ComboBox1_Change() Range("C5") = ComboBox1.Value TextBox1 = ComboBox1.Column(1) ' < this would be the second column of data ' This also gives the employee name without the VLookUp End Sub "TimN" wrote: I have a user form that has three text boxes along with an OK command and a Cancel command. The first textbox is an employee ID number, the next two are dates. The Employee ID is entered and then a VLOOKUP formula goes to another worksheet and finds the employee name and other data. If it doesn't find a name I have written into the formula to enter "Invalid Employee Number" in the cell. Here is the situation: If an invalid employee number is entered and the user doesn't notice bofore clicking OK, the "invalid" data is copied to another hidden worksheet and it is just a sheet of errors since the employee number wasn't valid. OR If the user enters a valid number, but doesn't notice until he/she hits OK that the wrong employee number was entered, then that wrong employee data is copied to the hidden worksheet. What is the best way to prevent either of these from getting copied before the user is certain the correct employee number is entered? I attempted to create a MsgBox at the first Input box asking "Is this the Correct Employee Number?". However the message box appeared after a single digit was entered. For example if the employee number is 1554, the message box popped up when you type 1, not at the end of typing 1554. What do you experts suggest???? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing incorrect data from being copied
The option of only providing valid data is preferable, as Rich pointed out.
Bit if you can't do that for reason, only enable the OK button if the textbox value is NOT "Invalid...". e.g. following the code that checks the input and insets "Invali.." if required: Const INVALIDID As string="Invalid ID" 'Other code cmdOK.Enabled=(txtEmplyeeID.Text<INVALIDID) NickHK "TimN" wrote in message ... I have a user form that has three text boxes along with an OK command and a Cancel command. The first textbox is an employee ID number, the next two are dates. The Employee ID is entered and then a VLOOKUP formula goes to another worksheet and finds the employee name and other data. If it doesn't find a name I have written into the formula to enter "Invalid Employee Number" in the cell. Here is the situation: If an invalid employee number is entered and the user doesn't notice bofore clicking OK, the "invalid" data is copied to another hidden worksheet and it is just a sheet of errors since the employee number wasn't valid. OR If the user enters a valid number, but doesn't notice until he/she hits OK that the wrong employee number was entered, then that wrong employee data is copied to the hidden worksheet. What is the best way to prevent either of these from getting copied before the user is certain the correct employee number is entered? I attempted to create a MsgBox at the first Input box asking "Is this the Correct Employee Number?". However the message box appeared after a single digit was entered. For example if the employee number is 1554, the message box popped up when you type 1, not at the end of typing 1554. What do you experts suggest???? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing incorrect data from being copied
Thanks for the ideas. I had thought about the list box. My hesitation is
there are 1,800 employees and all I am interested are those who are out on Short Term Disability, maybe just 10 or so at any given time. And further, this tool would be used by a department admin. who would typically only have one or maybe two people out from his/her department at any given time. It would be a big list to look through to find one or two people. Might still be a good option though. Thanks again for the help. "TimN" wrote: I have a user form that has three text boxes along with an OK command and a Cancel command. The first textbox is an employee ID number, the next two are dates. The Employee ID is entered and then a VLOOKUP formula goes to another worksheet and finds the employee name and other data. If it doesn't find a name I have written into the formula to enter "Invalid Employee Number" in the cell. Here is the situation: If an invalid employee number is entered and the user doesn't notice bofore clicking OK, the "invalid" data is copied to another hidden worksheet and it is just a sheet of errors since the employee number wasn't valid. OR If the user enters a valid number, but doesn't notice until he/she hits OK that the wrong employee number was entered, then that wrong employee data is copied to the hidden worksheet. What is the best way to prevent either of these from getting copied before the user is certain the correct employee number is entered? I attempted to create a MsgBox at the first Input box asking "Is this the Correct Employee Number?". However the message box appeared after a single digit was entered. For example if the employee number is 1554, the message box popped up when you type 1, not at the end of typing 1554. What do you experts suggest???? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing incorrect data from being copied
if they start typing, the number will fill in automatically so you would have
the best of both. I put error check in and seems to work well typing in the number. in module attached to whatever control calls your userform Sub EmployeeForm() On Error Resume Next UserForm1.Show End Sub in userform code Private Sub UserForm_Initialize() If Not IsEmpty(Range("C5")) Then ComboBox1 = Range("C5") TextBox1 = ComboBox1.Column(1) End If End Sub Private Sub ComboBox1_Change() On Error Resume Next Range("C5") = ComboBox1.Value TextBox1 = ComboBox1.Column(1) End Sub "TimN" wrote: Thanks for the ideas. I had thought about the list box. My hesitation is there are 1,800 employees and all I am interested are those who are out on Short Term Disability, maybe just 10 or so at any given time. And further, this tool would be used by a department admin. who would typically only have one or maybe two people out from his/her department at any given time. It would be a big list to look through to find one or two people. Might still be a good option though. Thanks again for the help. "TimN" wrote: I have a user form that has three text boxes along with an OK command and a Cancel command. The first textbox is an employee ID number, the next two are dates. The Employee ID is entered and then a VLOOKUP formula goes to another worksheet and finds the employee name and other data. If it doesn't find a name I have written into the formula to enter "Invalid Employee Number" in the cell. Here is the situation: If an invalid employee number is entered and the user doesn't notice bofore clicking OK, the "invalid" data is copied to another hidden worksheet and it is just a sheet of errors since the employee number wasn't valid. OR If the user enters a valid number, but doesn't notice until he/she hits OK that the wrong employee number was entered, then that wrong employee data is copied to the hidden worksheet. What is the best way to prevent either of these from getting copied before the user is certain the correct employee number is entered? I attempted to create a MsgBox at the first Input box asking "Is this the Correct Employee Number?". However the message box appeared after a single digit was entered. For example if the employee number is 1554, the message box popped up when you type 1, not at the end of typing 1554. What do you experts suggest???? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing incorrect data from being copied
Rich,
I understand now what you are saying and I like the idea. I implemented the code as you suggested and I get a compile error Variable not defined on the 4th line of code below ...TextEmployeeNumber = ComboBox1.Column(1) Here is my code with my specific names: Private Sub UserForm_Initialize() If Not IsEmpty(Range("C3")) Then ComboBox1 = Range("C3") TextEmployeeNumber = ComboBox1.Column(1) End If End Sub Private Sub ComboBox1_Change() On Error Resume Next Range("C3") = ComboBox1.Value TextEmployeeNumber = ComboBox1.Column(1) End Sub What am I doing wrong?? "Rich J" wrote: if they start typing, the number will fill in automatically so you would have the best of both. I put error check in and seems to work well typing in the number. in module attached to whatever control calls your userform Sub EmployeeForm() On Error Resume Next UserForm1.Show End Sub in userform code Private Sub UserForm_Initialize() If Not IsEmpty(Range("C5")) Then ComboBox1 = Range("C5") TextBox1 = ComboBox1.Column(1) End If End Sub Private Sub ComboBox1_Change() On Error Resume Next Range("C5") = ComboBox1.Value TextBox1 = ComboBox1.Column(1) End Sub "TimN" wrote: Thanks for the ideas. I had thought about the list box. My hesitation is there are 1,800 employees and all I am interested are those who are out on Short Term Disability, maybe just 10 or so at any given time. And further, this tool would be used by a department admin. who would typically only have one or maybe two people out from his/her department at any given time. It would be a big list to look through to find one or two people. Might still be a good option though. Thanks again for the help. "TimN" wrote: I have a user form that has three text boxes along with an OK command and a Cancel command. The first textbox is an employee ID number, the next two are dates. The Employee ID is entered and then a VLOOKUP formula goes to another worksheet and finds the employee name and other data. If it doesn't find a name I have written into the formula to enter "Invalid Employee Number" in the cell. Here is the situation: If an invalid employee number is entered and the user doesn't notice bofore clicking OK, the "invalid" data is copied to another hidden worksheet and it is just a sheet of errors since the employee number wasn't valid. OR If the user enters a valid number, but doesn't notice until he/she hits OK that the wrong employee number was entered, then that wrong employee data is copied to the hidden worksheet. What is the best way to prevent either of these from getting copied before the user is certain the correct employee number is entered? I attempted to create a MsgBox at the first Input box asking "Is this the Correct Employee Number?". However the message box appeared after a single digit was entered. For example if the employee number is 1554, the message box popped up when you type 1, not at the end of typing 1554. What do you experts suggest???? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing incorrect data from being copied
Where I had Textbox1 it was to show the name associated with the employee #.
Rename that to the name of the textbox on the form that has the Employee Name. The combobox has two columns. Employee # & Employee Name. It looks like you are adding the combobox and then trying to assign your existing textbox (TextEmployeeNumber) the value of the combobox. Combobox1.Value is whatever employee # is typed in the combobox or selected. The expression =ComboBox1.Column(1) is actually the second field which is the employee name. It should be the name of the textbox where the name is being shown. Don't put anything in the ControlSource in the Properties, the VB code puts the value in the cell. If you need to put the name in a cell next to the number then use Range("D3") = ComboBox1.Column(1). And, if TextEmployeeNumber is not the name of the existing textbox, VB is thinking you created a variable name. You said you had three textboxes. I misread and I thought one was employee #, one was employee name and a third was date. You could add another textbox on the form and the employee name will show right there. "TimN" wrote: Rich, I understand now what you are saying and I like the idea. I implemented the code as you suggested and I get a compile error Variable not defined on the 4th line of code below ...TextEmployeeNumber = ComboBox1.Column(1) Here is my code with my specific names: Private Sub UserForm_Initialize() If Not IsEmpty(Range("C3")) Then ComboBox1 = Range("C3") TextEmployeeNumber = ComboBox1.Column(1) End If End Sub Private Sub ComboBox1_Change() On Error Resume Next Range("C3") = ComboBox1.Value TextEmployeeNumber = ComboBox1.Column(1) End Sub What am I doing wrong?? "Rich J" wrote: if they start typing, the number will fill in automatically so you would have the best of both. I put error check in and seems to work well typing in the number. in module attached to whatever control calls your userform Sub EmployeeForm() On Error Resume Next UserForm1.Show End Sub in userform code Private Sub UserForm_Initialize() If Not IsEmpty(Range("C5")) Then ComboBox1 = Range("C5") TextBox1 = ComboBox1.Column(1) End If End Sub Private Sub ComboBox1_Change() On Error Resume Next Range("C5") = ComboBox1.Value TextBox1 = ComboBox1.Column(1) End Sub "TimN" wrote: Thanks for the ideas. I had thought about the list box. My hesitation is there are 1,800 employees and all I am interested are those who are out on Short Term Disability, maybe just 10 or so at any given time. And further, this tool would be used by a department admin. who would typically only have one or maybe two people out from his/her department at any given time. It would be a big list to look through to find one or two people. Might still be a good option though. Thanks again for the help. "TimN" wrote: I have a user form that has three text boxes along with an OK command and a Cancel command. The first textbox is an employee ID number, the next two are dates. The Employee ID is entered and then a VLOOKUP formula goes to another worksheet and finds the employee name and other data. If it doesn't find a name I have written into the formula to enter "Invalid Employee Number" in the cell. Here is the situation: If an invalid employee number is entered and the user doesn't notice bofore clicking OK, the "invalid" data is copied to another hidden worksheet and it is just a sheet of errors since the employee number wasn't valid. OR If the user enters a valid number, but doesn't notice until he/she hits OK that the wrong employee number was entered, then that wrong employee data is copied to the hidden worksheet. What is the best way to prevent either of these from getting copied before the user is certain the correct employee number is entered? I attempted to create a MsgBox at the first Input box asking "Is this the Correct Employee Number?". However the message box appeared after a single digit was entered. For example if the employee number is 1554, the message box popped up when you type 1, not at the end of typing 1554. What do you experts suggest???? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing incorrect data from being copied
Rich,
I will give it another try. I am very new to VBA, so I am just trying to find my way through this stuff. I appreciate your help. TimN "Rich J" wrote: Where I had Textbox1 it was to show the name associated with the employee #. Rename that to the name of the textbox on the form that has the Employee Name. The combobox has two columns. Employee # & Employee Name. It looks like you are adding the combobox and then trying to assign your existing textbox (TextEmployeeNumber) the value of the combobox. Combobox1.Value is whatever employee # is typed in the combobox or selected. The expression =ComboBox1.Column(1) is actually the second field which is the employee name. It should be the name of the textbox where the name is being shown. Don't put anything in the ControlSource in the Properties, the VB code puts the value in the cell. If you need to put the name in a cell next to the number then use Range("D3") = ComboBox1.Column(1). And, if TextEmployeeNumber is not the name of the existing textbox, VB is thinking you created a variable name. You said you had three textboxes. I misread and I thought one was employee #, one was employee name and a third was date. You could add another textbox on the form and the employee name will show right there. "TimN" wrote: Rich, I understand now what you are saying and I like the idea. I implemented the code as you suggested and I get a compile error Variable not defined on the 4th line of code below ...TextEmployeeNumber = ComboBox1.Column(1) Here is my code with my specific names: Private Sub UserForm_Initialize() If Not IsEmpty(Range("C3")) Then ComboBox1 = Range("C3") TextEmployeeNumber = ComboBox1.Column(1) End If End Sub Private Sub ComboBox1_Change() On Error Resume Next Range("C3") = ComboBox1.Value TextEmployeeNumber = ComboBox1.Column(1) End Sub What am I doing wrong?? "Rich J" wrote: if they start typing, the number will fill in automatically so you would have the best of both. I put error check in and seems to work well typing in the number. in module attached to whatever control calls your userform Sub EmployeeForm() On Error Resume Next UserForm1.Show End Sub in userform code Private Sub UserForm_Initialize() If Not IsEmpty(Range("C5")) Then ComboBox1 = Range("C5") TextBox1 = ComboBox1.Column(1) End If End Sub Private Sub ComboBox1_Change() On Error Resume Next Range("C5") = ComboBox1.Value TextBox1 = ComboBox1.Column(1) End Sub "TimN" wrote: Thanks for the ideas. I had thought about the list box. My hesitation is there are 1,800 employees and all I am interested are those who are out on Short Term Disability, maybe just 10 or so at any given time. And further, this tool would be used by a department admin. who would typically only have one or maybe two people out from his/her department at any given time. It would be a big list to look through to find one or two people. Might still be a good option though. Thanks again for the help. "TimN" wrote: I have a user form that has three text boxes along with an OK command and a Cancel command. The first textbox is an employee ID number, the next two are dates. The Employee ID is entered and then a VLOOKUP formula goes to another worksheet and finds the employee name and other data. If it doesn't find a name I have written into the formula to enter "Invalid Employee Number" in the cell. Here is the situation: If an invalid employee number is entered and the user doesn't notice bofore clicking OK, the "invalid" data is copied to another hidden worksheet and it is just a sheet of errors since the employee number wasn't valid. OR If the user enters a valid number, but doesn't notice until he/she hits OK that the wrong employee number was entered, then that wrong employee data is copied to the hidden worksheet. What is the best way to prevent either of these from getting copied before the user is certain the correct employee number is entered? I attempted to create a MsgBox at the first Input box asking "Is this the Correct Employee Number?". However the message box appeared after a single digit was entered. For example if the employee number is 1554, the message box popped up when you type 1, not at the end of typing 1554. What do you experts suggest???? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Disable spreadsheet when copied to incorrect file path or differen ip address | Excel Discussion (Misc queries) | |||
Preventing data input until a particular cell is used | Excel Worksheet Functions | |||
Preventing Data Import | Excel Discussion (Misc queries) | |||
Preventing duplicate data | Excel Discussion (Misc queries) | |||
Preventing a workbook from being copied | Excel Programming |