How to push user to fill cell after using drop down list.

[Ksiendzu]

I'm doing order form. I would like to be sure that always after use of drop down list of products, quantity od them will be enter by user.
a1..a30 drop down list of products in every cell ( that part I know how to do)
b1..b30 quantity to be put by user (1-100)

User is choosing product form drop down list, and next possible action should be insert of quantity of that product.

How to force user to put that quantity I have no idea.
Thanks for help.

 

[James006]

Hello,

You probably need a named range ...

see link: http://blog.contextures.com/archives/2009/05/10/jump-to-the-next-data-entry-cell-in-excel/

 

[tonyyy]

Ksiendzu,

Welcome to the Board.

If you're comfortable with a vba approach, you might consider the following...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim qty As String
On Error GoTo errHandler
Application.EnableEvents = False
If Not Intersect(Target, Range("A1:A30")) Is Nothing And Target.Value <> "" Then
    Do
        qty = InputBox(prompt:="Please enter a quantity." & vbCrLf & "1 to 100", Title:="Required Field")
        Cells(Target.Row, 2) = qty
    Loop While qty = vbNullString
End If
errHandler:
Application.EnableEvents = True
End Sub

The code should be pasted into the appropriate worksheet module.

When a user enters/chooses a product, an input box will display that requires the user to input a quantity.

Cheers,

tonyyy

 

[Ksiendzu]

Thank you very much. Works fine. I was playing with Basic on my Atari long time ago. Didn't even notice that it is still alive. Thank you very much for your help.

 

[tonyyy]

You're welcome. Glad it worked out...