I have a set of input boxes that all allow a number to be added. The entries
can be:
a. Correct (proceed to the next step)
b. Wrong (doesn't meet a criteria - re-input until correct or blank
c. Blank
The first number can't be blank; subsequent entries can be. If
blank, proceed to the next step*

There are a several ways to do this, including in my order of preference:.
1. Have a multiple-entry input box that would allow input of several
numbers, leaving the others blank.
2. Make it a separate subroutine.
3. Use Cases (I haven't tried this, yet)
4. imbed it in the regular code - what I'm doing, but I'm having difficulty
getting 'out'* on blank entries

Questions:
1. is a multi-entry input box a widget that is available? If so, how can I
get it?
2. What do you experts think is the 'best' approach?
3. How do I get out of a code section without getting into a series of GoTo
statements, that seem to create other problems?

Here's what I'm working with right now...

Do
areaCode2 = InputBox("Enter a Second Area Code, (if required)", "Area
Code 2", "", 80)
If areaCode2 = "" Then GoTo XXX

Set wb = ThisWorkbook
On Error Resume Next
Set wb2 = Workbooks.Open(Filename:=Environ("Userprofile") &
"\Desktop\" & areaCode2 & ".csv", ReadOnly:=True)
If wb2 Is Nothing Then
msg = MsgBox("The Area Code " & areaCode2 & " file does not
exist!" & vbLf & vbLf & "Please try again.", vbExclamation, "Input error")

Else
wb2OK = True
End If
On Error GoTo 0 ' (is '0' the beginning of all the code, or
the 'Do'?)
Loop Until wb2OK = True
XXX:

Suggestions?

Jim Berglund

Hi Jim,

The following is an example of what you might use. Some comments.
Don't use GoTo to get out of a loop. Use Exit Do.
Try to give the user an Out by Cancelling at the inut stage. Users need to
be able to Cancel and exit a procedure. (Note Application.InputBox is
different from InputBox function. See Help for more info)
You can use Select Case in lieu of multiple If/End If.

Feel free to get back to me if still having problems.

Sub test()
Dim areaCode2 As Variant
Dim wb As Workbook
Dim wb2 As Workbook
Dim msg

Do
'Use Application.InputBox in lieu of just InputBox
'Allows identification of Cancel by user.
areaCode2 = Application.InputBox _
("Enter a Second Area Code, (if required)", _
"Area Code 2", "", 80)

'Following allows user to click Cancel and abort
If areaCode2 = False Then
MsgBox "User Cancelled. Processing terminated"
Exit Sub
End If

If areaCode2 = "" Then Exit Do 'Exit the Loop

Set wb = ThisWorkbook 'Not sure why this is here

On Error Resume Next
Set wb2 = Workbooks.Open _
(Filename:=Environ("Userprofile") & _
"\Desktop\" & areaCode2 & ".csv", _
ReadOnly:=True)

If wb2 Is Nothing Then
msg = MsgBox("The Area Code " & areaCode2 & _
" file does not exist!" & vbLf & vbLf & _
"Please try again.", vbExclamation, "Input error")

'GoTo LoopAgain
End If

'Use the GoTo LoopAgain if other code here to be
'skipped if wb2 is nothing.

'LoopAgain:
Loop

End Sub


--
Regards,

OssieMac


"Jim Berglund" wrote:

I have a set of input boxes that all allow a number to be added. The entries
can be:
a. Correct (proceed to the next step)
b. Wrong (doesn't meet a criteria - re-input until correct or blank
c. Blank
The first number can't be blank; subsequent entries can be. If
blank, proceed to the next step*

There are a several ways to do this, including in my order of preference:.
1. Have a multiple-entry input box that would allow input of several
numbers, leaving the others blank.
2. Make it a separate subroutine.
3. Use Cases (I haven't tried this, yet)
4. imbed it in the regular code - what I'm doing, but I'm having difficulty
getting 'out'* on blank entries

Questions:
1. is a multi-entry input box a widget that is available? If so, how can I
get it?
2. What do you experts think is the 'best' approach?
3. How do I get out of a code section without getting into a series of GoTo
statements, that seem to create other problems?

Here's what I'm working with right now...

Do
areaCode2 = InputBox("Enter a Second Area Code, (if required)", "Area
Code 2", "", 80)
If areaCode2 = "" Then GoTo XXX

Set wb = ThisWorkbook
On Error Resume Next
Set wb2 = Workbooks.Open(Filename:=Environ("Userprofile") &
"\Desktop\" & areaCode2 & ".csv", ReadOnly:=True)
If wb2 Is Nothing Then
msg = MsgBox("The Area Code " & areaCode2 & " file does not
exist!" & vbLf & vbLf & "Please try again.", vbExclamation, "Input error")

Else
wb2OK = True
End If
On Error GoTo 0 ' (is '0' the beginning of all the code, or
the 'Do'?)
Loop Until wb2OK = True
XXX:

Suggestions?

Jim Berglund

.