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
>
> .
>
|
Similar Threads
