[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?

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

[Claus Busch]

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

[Claus Busch]

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

[L. Howard]

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

[L. Howard]

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

[L. Howard]

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

[Claus Busch]

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

[L. Howard]

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

[GS[_6_]]

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

[L. Howard]

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

[Claus Busch]

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

[GS[_6_]]

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

[L. Howard]

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

[GS[_6_]]

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

[L. Howard]

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

[GS[_6_]]

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

[L. 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



That would be fine.
Thanks.

Howard

[GS[_6_]]

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

[L. 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


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

[GS[_6_]]

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

[GS[_6_]]

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