How to select from a list?

[DRS]

What is the best was to accomplish the following:

A macro has a user entering data in various cells, when
they get to say, B5, I need a list box of some sort to
pop up that they can select an option. I do not want the
user to use the mouse, kbd only.
So they get to B5, a list of 5 options pops up, they can
arrow down to the third option and enter or hit 3 and
that value fills the cell and continues to the next cell
in the macro.

Example:

Range("B4").Select
CustName = InputBox("Enter Customer
Name", "CustName", "", 1, 1)
ActiveCell.Value = CustName

Range("B5").Select
'Here is where a list box pops up
'Once done, moves on

Range("B6").Select
PartNum = InputBox("Enter Part Number", "PartNum", "", 1,
1)
ActiveCell.Value = PartNum



Thanks

DRS

 

[Dave Peterson]

Just a keyboard--maybe using an inputbox would work ok?

Option Explicit
Sub testme()

Dim res As Long
Dim myChoices(1 To 5) As String
Dim myPrompt As String
Dim iCtr As Long

myChoices(1) = "Item 1"
myChoices(2) = "Item 2"
myChoices(3) = "Item 3"
myChoices(4) = "Item 4"
myChoices(5) = "Item 5"

myPrompt = ""
For iCtr = LBound(myChoices) To UBound(myChoices)
myPrompt = myPrompt & vbLf & iCtr & ". " & myChoices(iCtr)
Next iCtr

myPrompt = Mid(myPrompt, 2)

Do
res = CLng(Application.InputBox( _
prompt:=myPrompt, Type:=1))

If res >= 0 _
And res < 6 Then
Exit Do
Else
MsgBox "1 to 5 only!"
End If
Loop

If res = 0 Then
MsgBox "Nothing chosen (cancel?"
Else
MsgBox myChoices(res) & " was chosen"
End If

End Sub

If all your options don't fit, maybe a userform with all the choices on
it???????