Manage errors with multiple InPut Boxes
What would be the proper way to manage errors for a multitude of input boxes entering Strings or Longs, boxes have OK, Cancel & Red "X".
Click OK with no entry produces Type Mismatch, as does Cancel. Or one could enter text in a Long Dimmed input box. Does it make sense to have a "On Error GoTo" for each input box to go back to the same box with a MsgBox prompt? Thanks, Howard Sub EmployeeDataEnter() Dim lngLstRow& Dim EmpID As Long, ContNo As Long, SectNo As Long Dim strName As String, strEmpTyp As String, strPosTitle _ As String, strRepoMF As String EmpID = InputBox("Employee ID No.", "Employee ID") strName = InputBox("Name:", "Name of racer/owner") strEmpTyp = InputBox("Type" & vbNewLine & _ "Full Time" & vbNewLine & _ "Contract" & vbNewLine & _ "Other", "Employee Type") strPosTitle = InputBox("Type:" & vbNewLine & _ "Worker" & vbNewLine & _ "Clerical" & vbNewLine & _ "Exhibition", "Title of Worker:") strRepoMF = InputBox("Male - Female" & vbNewLine & _ "Female" & vbNewLine & _ "Male", "Repo-M/F") ContNo = InputBox("Employee Contact No.", "Contact") SectNo = InputBox("Section:", "Section") ' For a date if needed ' strDate = InputBox("Date:", "Date Enter") ' Data from input boxes added to the Master sheet" With Sheets("Master") lngLstRow = .UsedRange.Rows.Count + .UsedRange.Row .Range("A" & lngLstRow).Value = EmpID .Range("B" & lngLstRow).Value = strName .Range("C" & lngLstRow).Value = strEmpTyp .Range("D" & lngLstRow).Value = strPosTitle .Range("E" & lngLstRow).Value = strRepoMF .Range("F" & lngLstRow).Value = ContNo .Range("I" & lngLstRow).Value = SectNo End With 'Sheets("Sheet1").Select End Sub |
Manage errors with multiple InPut Boxes
Hi Howard,
Am Mon, 28 Sep 2015 01:42:03 -0700 (PDT) schrieb L. Howard: What would be the proper way to manage errors for a multitude of input boxes entering Strings or Longs, boxes have OK, Cancel & Red "X". Click OK with no entry produces Type Mismatch, as does Cancel. Or one could enter text in a Long Dimmed input box. Does it make sense to have a "On Error GoTo" for each input box to go back to the same box with a MsgBox prompt? use an Application.InputBox. With this box you can define the type of data. Your normal InputBox always return text.So you get an error if declared as long. What about all inputs in one InputBox comma separated. And then change that string to an array and work with this array? Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
Manage errors with multiple InPut Boxes
Hi again,
Am Mon, 28 Sep 2015 01:42:03 -0700 (PDT) schrieb L. Howard: What would be the proper way to manage errors for a multitude of input boxes entering Strings or Longs, boxes have OK, Cancel & Red "X". or create a UserForm with seven TextBoxes and a CommandButton. With a macro start the UserForm. With the CommandButton write the values to the sheet. You can check the TextBoxes for length. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
Manage errors with multiple InPut Boxes
use an Application.InputBox. With this box you can define the type of data. Your normal InputBox always return text.So you get an error if declared as long. What about all inputs in one InputBox comma separated. And then change that string to an array and work with this array? Regards Claus B. -- Hi Claus, Yes, the comma separated method should work, the out-put is in consecutive columns on Master sheet. But is that also possible if a column or two need to be skipped you can do that? I think I have some code with the comma separated method, I will give that a shot. Thanks. Howard |
Manage errors with multiple InPut Boxes
I think I have some code with the comma separated method, I will give that a shot. Hi Claus, I got it this far and now, I am bogged down. Object required error, but I also got lost with the Redim and the transpose out put Howard Sub Inputbox_Comma() Dim Empl_Info, i As Long Dim myArr As Variant Dim arrC As Range Empl_Info = InputBox("Enter Employee Info, separated by commas") For Each arrC In Empl_Info ReDim Preserve myArr(Empl_Info.Cells.Count - 1) myArr(i) = arrC i = i + 1 Next With Sheets("Master") .Range("A2").Resize(rowsize:=arrC.Cells.Count) _ = WorksheetFunction.Transpose(myArr) End With End Sub |
Manage errors with multiple InPut Boxes
I actually need each to offset from previous entries...
With Sheets("Master") Sheets("Master").Range("A" & Rows.Count).End(xlUp)(2).Resize(rowsize:=arrC.Cell s.Count) _ = WorksheetFunction.Transpose(myArr) End With Howard |
Manage errors with multiple InPut Boxes
Hi Howard,
Am Mon, 28 Sep 2015 05:10:22 -0700 (PDT) schrieb L. Howard: With Sheets("Master") Sheets("Master").Range("A" & Rows.Count).End(xlUp)(2).Resize(rowsize:=arrC.Cell s.Count) _ = WorksheetFunction.Transpose(myArr) End With sorry for the delay. I was not at home. Try: Sub Inputbox_Comma() Dim Empl_Info, i As Long Dim myArr As Variant Empl_Info = InputBox("Enter Employee Info, separated by commas") myArr = Split(Empl_Info, ",") With Sheets("Master") .Cells(Rows.Count, 1).End(xlUp)(2) _ .Resize(columnsize:=UBound(myArr) + 1) = myArr End With End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
Manage errors with multiple InPut Boxes
sorry for the delay. I was not at home.
Try: Sub Inputbox_Comma() Dim Empl_Info, i As Long Dim myArr As Variant Empl_Info = InputBox("Enter Employee Info, separated by commas") myArr = Split(Empl_Info, ",") With Sheets("Master") .Cells(Rows.Count, 1).End(xlUp)(2) _ .Resize(columnsize:=UBound(myArr) + 1) = myArr End With End Sub Regards Claus B. -- Perfect, as always! Thanks. Howard |
Manage errors with multiple InPut Boxes
I like to display a userform when collecting multiple fields of data
input. This just makes it a better experience for users over having to respond to multiple inputbox prompts. You can validate each input field (TextBox/DatePicker/SpinCountr or whatever) before proceeding, forcing the user to provide only valid data or cancel! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Manage errors with multiple InPut Boxes
On Monday, September 28, 2015 at 5:59:21 AM UTC-7, GS wrote:
I like to display a userform when collecting multiple fields of data input. This just makes it a better experience for users over having to respond to multiple inputbox prompts. You can validate each input field (TextBox/DatePicker/SpinCountr or whatever) before proceeding, forcing the user to provide only valid data or cancel! -- Garry Hi Garry, Here is what I'm using, it has a combination of text and ID numbers, perhaps an ID "number" may have a letter something like 1234ER, and Section is a number all others are strings. Out put is always text but is not used in formulas in this useage. Howard Sub Inputbox_Comma() Dim Empl_Info, i As Long Dim myArr As Variant Empl_Info = Application.InputBox(prompt:="Use a comma ( , ) as Delimiter" & vbCr & vbCr & _ "Example - 12345,Name,Type etc." & vbCr & _ "and a SPACE to skip an entry." & vbCr & vbCr & _ "1 - Employee ID" & vbCr & _ "2 - Name" & vbCr & _ "3 - Title " & vbCr & _ "5 - M/F Reproductive" & vbCr & _ "6 - Contact" & vbCr & _ "7 - Division" & vbCr & _ "8 - Deptartment" & vbCr & _ "9 - Section" & vbCr & _ "10 - Supervisor" & vbCr & _ "11 - Crew" & vbCr & _ "12 - Role Description" & vbCr, _ Title:="Employee Information New Entry", Type:=2) If Len(Empl_Info) = 0 Then MsgBox "No Entry" Exit Sub ElseIf Empl_Info = False Then Exit Sub End If myArr = Split(Empl_Info, ",") With Sheets("Master") .Cells(Rows.Count, 1).End(xlUp)(2) _ .Resize(columnsize:=UBound(myArr) + 1) = myArr End With End Sub |
Manage errors with multiple InPut Boxes
Hi Howard,
Am Mon, 28 Sep 2015 07:21:48 -0700 (PDT) schrieb L. Howard: Out put is always text but is not used in formulas in this useage. change the last part of the code to change numbers to real numbers: If Len(Empl_Info) = 0 Or Empl_Info = False Then MsgBox "No Entry" Exit Sub End If myArr = Split(Empl_Info, ",") With Sheets("Master") Set Dest = .Cells(Rows.Count, 1).End(xlUp)(2) For i = LBound(myArr) To UBound(myArr) If IsNumeric(myArr(i)) Then Dest.Offset(, i) = CLng(myArr(i)) Else Dest.Offset(, i) = myArr(i) End If Next End With I guess a UserForm will be more readable and easier to use. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
Manage errors with multiple InPut Boxes
Claus and I outnumber you about using a userform with labeled input
fields. This will be easier for the user as well as less prone to error if commas are not in the right places! You can input directly from each field via event code -OR- validate each field's data via a button click when done. Much easier to manage data without any ambiguity, with or without using an array to write to the sheet. If using an array you can store the field's index in its 'Tag' property and myArr(CLng(.Tag)) to load its value. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Manage errors with multiple InPut Boxes
On Monday, September 28, 2015 at 9:21:48 AM UTC-7, GS wrote:
Claus and I outnumber you about using a userform with labeled input fields. This will be easier for the user as well as less prone to error if commas are not in the right places! You can input directly from each field via event code -OR- validate each field's data via a button click when done. Much easier to manage data without any ambiguity, with or without using an array to write to the sheet. If using an array you can store the field's index in its 'Tag' property and myArr(CLng(.Tag)) to load its value. -- Garry I guess I need to bone up on userforms, not very versed on them at present. Thanks. Howars |
Manage errors with multiple InPut Boxes
On Monday, September 28, 2015 at 9:21:48 AM UTC-7, GS wrote:
Claus and I outnumber you about using a userform with labeled input fields. This will be easier for the user as well as less prone to error if commas are not in the right places! You can input directly from each field via event code -OR- validate each field's data via a button click when done. Much easier to manage data without any ambiguity, with or without using an array to write to the sheet. If using an array you can store the field's index in its 'Tag' property and myArr(CLng(.Tag)) to load its value. -- Garry I guess I need to bone up on userforms, not very versed on them at present. Thanks. Howars I'll see if I can muster up a sample using your fields... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Manage errors with multiple InPut Boxes
I'll see if I can muster up a sample using your fields... -- Garry That would be great, a training aid so to say. Thanks. Howard |
Manage errors with multiple InPut Boxes
I'll see if I can muster up a sample using your fields... -- Garry That would be great, a training aid so to say. Thanks. Howard Well.., I should keep it very basic then. (user input to worksheet range) N.B.: my modem is acting up and so the cable company is coming Friday to fix/replace. I'll have only intermittent internet access until then! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Manage errors with multiple InPut Boxes
Well.., I should keep it very basic then. (user input to worksheet range) N.B.: my modem is acting up and so the cable company is coming Friday to fix/replace. I'll have only intermittent internet access until then! -- Garry That would be fine. Thanks. Howard |
Manage errors with multiple InPut Boxes
Well.., I should keep it very basic then. (user input to worksheet range) N.B.: my modem is acting up and so the cable company is coming Friday to fix/replace. I'll have only intermittent internet access until then! -- Garry That would be fine. Thanks. Howard Hi Howard, I haven't mustered the energy yet to do a separate example project, but as an afterthought you might want to try using Excel's DataForm. Just make sure the data table has headers and fire the form up for user input. Worth a try since it's built-in specifically for that purpose. (The sheet need not even be visible if the dialog is code launched!) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Manage errors with multiple InPut Boxes
Hi Howard, I haven't mustered the energy yet to do a separate example project, but as an afterthought you might want to try using Excel's DataForm. Just make sure the data table has headers and fire the form up for user input. Worth a try since it's built-in specifically for that purpose. (The sheet need not even be visible if the dialog is code launched!) -- Garry Hi Garry, I gave Excel's DataForm a try and works quite well, after finding a tutorial that stepped me through it, along with the features and such. Thanks, and take care. Howard |
Manage errors with multiple InPut Boxes
Hi Howard, I haven't mustered the energy yet to do a separate example project, but as an afterthought you might want to try using Excel's DataForm. Just make sure the data table has headers and fire the form up for user input. Worth a try since it's built-in specifically for that purpose. (The sheet need not even be visible if the dialog is code launched!) -- Garry Hi Garry, I gave Excel's DataForm a try and works quite well, after finding a tutorial that stepped me through it, along with the features and such. Thanks, and take care. Howard That's great, Howard! Appreciate the feedback... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Manage errors with multiple InPut Boxes
Hi Howard, I haven't mustered the energy yet to do a separate example project, but as an afterthought you might want to try using Excel's DataForm. Just make sure the data table has headers and fire the form up for user input. Worth a try since it's built-in specifically for that purpose. (The sheet need not even be visible if the dialog is code launched!) -- Garry Hi Garry, I gave Excel's DataForm a try and works quite well, after finding a tutorial that stepped me through it, along with the features and such. Thanks, and take care. Howard You can get J-Walks enhanced DataForm addin here... http://spreadsheetpage.com/index.php/dataform/home ...which includes source code. You may find this useful... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
All times are GMT +1. The time now is 09:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com