Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #108039 > unrolled thread

Manage errors with multiple InPut Boxes

Back to article view | Back to microsoft.public.excel.programming

Contents

  Manage errors with multiple InPut Boxes "L. Howard" <lhkittle@comcast.net> - 2015-09-28 01:42 -0700
    Re: Manage errors with multiple InPut Boxes Claus Busch <claus_busch@t-online.de> - 2015-09-28 12:22 +0200
      Re: Manage errors with multiple InPut Boxes "L. Howard" <lhkittle@comcast.net> - 2015-09-28 04:15 -0700
        Re: Manage errors with multiple InPut Boxes "L. Howard" <lhkittle@comcast.net> - 2015-09-28 05:05 -0700
          Re: Manage errors with multiple InPut Boxes "L. Howard" <lhkittle@comcast.net> - 2015-09-28 05:10 -0700
            Re: Manage errors with multiple InPut Boxes Claus Busch <claus_busch@t-online.de> - 2015-09-28 14:26 +0200
              Re: Manage errors with multiple InPut Boxes "L. Howard" <lhkittle@comcast.net> - 2015-09-28 05:48 -0700
    Re: Manage errors with multiple InPut Boxes Claus Busch <claus_busch@t-online.de> - 2015-09-28 12:28 +0200
    Re: Manage errors with multiple InPut Boxes GS <gs@v.invalid> - 2015-09-28 08:59 -0400
      Re: Manage errors with multiple InPut Boxes "L. Howard" <lhkittle@comcast.net> - 2015-09-28 07:21 -0700
        Re: Manage errors with multiple InPut Boxes Claus Busch <claus_busch@t-online.de> - 2015-09-28 17:43 +0200
        Re: Manage errors with multiple InPut Boxes GS <gs@v.invalid> - 2015-09-28 12:21 -0400
          Re: Manage errors with multiple InPut Boxes "L. Howard" <lhkittle@comcast.net> - 2015-09-28 09:45 -0700
            Re: Manage errors with multiple InPut Boxes GS <gs@v.invalid> - 2015-09-28 17:54 -0400
              Re: Manage errors with multiple InPut Boxes "L. Howard" <lhkittle@comcast.net> - 2015-09-29 19:01 -0700
                Re: Manage errors with multiple InPut Boxes GS <gs@v.invalid> - 2015-09-30 20:36 -0400
                  Re: Manage errors with multiple InPut Boxes "L. Howard" <lhkittle@comcast.net> - 2015-10-01 00:15 -0700
                    Re: Manage errors with multiple InPut Boxes GS <gs@v.invalid> - 2015-10-02 14:40 -0400
                      Re: Manage errors with multiple InPut Boxes "L. Howard" <lhkittle@comcast.net> - 2015-10-02 13:41 -0700
                        Re: Manage errors with multiple InPut Boxes GS <gs@v.invalid> - 2015-10-02 16:46 -0400
                        Re: Manage errors with multiple InPut Boxes GS <gs@v.invalid> - 2015-10-03 13:21 -0400

Page 1 of 2  [1] 2  Next page →

#108039 — 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

[toc] | [next] | [standalone]

#108040

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

[toc] | [prev] | [next] | [standalone]

#108042

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

[toc] | [prev] | [next] | [standalone]

#108043

> 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

[toc] | [prev] | [next] | [standalone]

#108044

I actually need each to offset from previous entries...

With Sheets("Master")
  Sheets("Master").Range("A" & Rows.Count).End(xlUp)(2).Resize(rowsize:=arrC.Cells.Count) _
         = WorksheetFunction.Transpose(myArr)
End With

Howard

[toc] | [prev] | [next] | [standalone]

#108045

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.Cells.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

[toc] | [prev] | [next] | [standalone]

#108046

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

[toc] | [prev] | [next] | [standalone]

#108041

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

[toc] | [prev] | [next] | [standalone]

#108047

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

[toc] | [prev] | [next] | [standalone]

#108048

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
  

[toc] | [prev] | [next] | [standalone]

#108049

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

[toc] | [prev] | [next] | [standalone]

#108050

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

[toc] | [prev] | [next] | [standalone]

#108051

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

[toc] | [prev] | [next] | [standalone]

#108052

> 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

[toc] | [prev] | [next] | [standalone]

#108053

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

[toc] | [prev] | [next] | [standalone]

#108054

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

[toc] | [prev] | [next] | [standalone]

#108055

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

[toc] | [prev] | [next] | [standalone]

#108056

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

[toc] | [prev] | [next] | [standalone]

#108057

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

[toc] | [prev] | [next] | [standalone]

#108058

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

[toc] | [prev] | [next] | [standalone]

Page 1 of 2  [1] 2  Next page →

Back to top | Article view | microsoft.public.excel.programming

csiph-web