Limit cell value based on a drop down list

[little jimmy]

Hi,

I am trying to redesign an expenses form for work and need to put certain limits on meal types.

You can claim for breakfast, lunch and dinner and the maximum amount you can claim is £5, £5 and £18 respectively. The meal options are selected from a drop down list (there are other items in the list such as taxi, car park, bus etc..but these do not have limits to their value).

If someone wants to claim for breakfast they will choose that from the drop down list but if they enter a value greater than £5 I want a warning message to pop up (just like data validation) saying something like "breakfast amount exceeded" or "dinner amount exceeded" and will not allow them to enter more than £5. Likewise with lunch £5 and Dinner £18, but if they choose taxi or bus or other option from the drop down the value is not restricted.

Hope this makes sense.

 

[mumps]

In which cell is the drop down list and in which cell do they enter the dinner amount?

 

[little jimmy]

I have used the HTML maker and posted the code below. I'm not sure how this code stuff works so I will try to describe the layout (just in case).
The expense type is in column B and the data validation list is kept in column I. You will click in cell b2 and choose an option from the drop down list (say breakfast) then you will type the amount in column C so in C2 you will put £3.50. If you put £6 (or anything over £5) you will get an error message and it will stop you from entering anything over £5.
The problem is they could choose any item from the drop down list and the limit can be £5, £18 or any value. Then you choose your next expense in cell B3, B4,B5 etc...

Thanks

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1	Date	expense type	amount						Data validation
2	01/03/2018	breakfast	£2.50
3	01/03/2018	lunch	£4.99						breakfast
4	01/03/2018	dinner	£12.99						lunch
5	02/03/2018	breakfast	£5.50						dinner
6	02/03/2018	lunch	£4.99						taxi
7		dinner	£18.00						bus
8		taxi	£35.00						car park
9		car park	£22.00						misc
10
Sheet1

Cell Formulas
Range	Formula
A1	Date
A2	43160
A3	43160
A4	43160
A5	43161
A6	43161
B1	expense type
B2	breakfast
B3	lunch
B4	dinner
B5	breakfast
B6	lunch
B7	dinner
B8	taxi
B9	car park
C1	amount
C2	2.5
C3	4.99
C4	12.99
C5	5.5
C6	4.99
C7	18
C8	35
C9	22
I1	Data validation
I3	breakfast
I4	lunch
I5	dinner
I6	taxi
I7	bus
I8	car park
I9	misc

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in column B and then enter a number in column C and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    Select Case Target.Offset(0, -1).Value
        Case "breakfast", "lunch"
            If Target > 5 Then
                Target.ClearContents
                MsgBox Target.Offset(0, -1) & " amount exceeded.  Please enter a maximum of 5."
                Target.Select
            End If
        Case "dinner"
            If Target > 18 Then
                Target.ClearContents
                MsgBox Target.Offset(0, -1) & " amount exceeded.  Please enter a maximum of 18."
                Target.Select
            End If
    End Select
End Sub

 

[little jimmy]

Fantastic, that does the job.

Many many thanks !!

 

[mumps]

You are very welcome.