Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create formulas using multiple pop-up input boxes? | Excel Programming | |||
VBA to manage user input in a workbook | Excel Programming | |||
Using excel to manage event - ANY input deeply appreciated! :-) | Excel Discussion (Misc queries) | |||
How do I add input data in the input ranges in drop down boxes. | Excel Discussion (Misc queries) | |||
Mask input in input boxes? | Excel Programming |