InputBox with multiple answers

[WLMPilot]

Below is a macro that I use to control cursor movement on an order form. I
placed ** next to the sections that deal with my question. Currently I have
a spreadsheet that users can order control drugs (sections w/ **) and regular
supplies (Range A14:B90). The control drugs are ordered per ambulance (M-13,
M-9, M-18, etc).

Currently, the user enters the ambulance number in E7:H7 and normally only
E7 & F7 would be used. However, I left enough for two more ambulances. Cells
E8:E9, F8:F9, etc will contain the quantity needed to replace stock for the
two control drugs.

What I want to know is if there is a way to have an InputBox or something
that ask the user for each ambulance number that needs control drugs
replaced. This answer may have multiple answers, ie M-13, M-18, M-4. Then I
want to insert each ambulance number into E7, F7, G7, H7 (depending on
answers) and place the cursor in E8 to begin entering quantities. I also
want to bypass the range not used. Ex: If there is only two ambulances that
need control drugs, then bypass G7:H9 and use only E7:F9.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Ans As Long
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub

**'Entry in B4
If Target.Address(0, 0) = "B4" Then
Ans = MsgBox("Do you have any control drugs to order?.", 4, "Verify
order for control drugs")
Application.EnableEvents = False
If Ans = vbYes Then
Range("D2").Value = "Yes"
Range("E7").Select 'Beginning of Control Drug Order
Section
Else
Range("D2").Value = "No"
Range("A14").Select
End If
Application.EnableEvents = True
Exit Sub
End If

**'Entry in E9:G9 - Moves cursor to next column (row 7)
If Not Intersect(Target, Range("E9:G9")) Is Nothing Then
Target.Offset(-2, 1).Select
Exit Sub
End If

**'Entry in H9 - Moves curor to main order form
If Target.Address(0, 0) = "H9" Then
Range("A14").Select
Exit Sub
End If

'Entry in A14:B90
If Not Intersect(Target, Range("A14:B90")) Is Nothing Then
If Target.Column = 1 Then
Target.Offset(, 1).Select
Else
Target.Offset(1, -1).Select
End If
End If
End Sub



Thanks for your help,
Les

 

[John Coleman]

Maybe the following code fragment might help:

Sub GetAmbulances()
Dim ambulances As Variant
Dim ambString As String
Dim i As Long

ambString = InputBox("Enter ambulance numbers, separated by
commas")
ambulances = Split(ambString, ",")
For i = 0 To UBound(ambulances)
Range("E7").Offset(0, i).Value = Trim(ambulances(i))
Next i
Range("E8").Select
End Sub

HTH

-John Coleman